Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

MySQL doesn't have nearest neighbor search so. So I try to come up with a "distance" that's reasonable so the number of returned query is around 20-200. That way innodb doesn't have to compute distance for like 30k rows if we only need 20.

Well, even after that, queries still take 13 seconds.

SELECT TB.ID ,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  MBRContains(
        GeomFromText (
            'MULTIPOINT(-6.1928749092968 106.7651250907,-6.1771250907032 106.7808749093)'
            ),
            TA.Latlong) 
  AND MATCH (TA.FullTextSearch) AGAINST ('re*' IN BOOLEAN MODE) 
AND TA.Prominent >15 
ORDER BY
  Distance

Number of matches are 32 by the way.

Showing rows 0 - 29 ( 32 total, Query took 16.1845 sec)

Also an even simpler query where we do not compute distance also take 16 seconds

SELECT TB.ID
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  MBRContains(
        GeomFromText (
            'MULTIPOINT(-6.1928749092968 106.7651250907,-6.1771250907032 106.7808749093)'
            ),
            TA.Latlong) 
  AND MATCH (TA.FullTextSearch) AGAINST ('res*' IN BOOLEAN MODE)    
AND TA.Prominent >15 

Actually if I do not compute distance query is reasonably fast and I can tell client to compute distance anyway.

I can understand if mysql cannot do nearest neighbor search. What I am doing now is a simple search points in a box here. What am I missing?

The explanation for the query is:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  TA  fulltext    PRIMARY,Prominent,LatLong,LatLong_2,FullTextSearch  FullTextSearch  0       1   Using where
1   SIMPLE  TB  eq_ref  PRIMARY     PRIMARY     752     gensurv_isikota.TA.BusinessID   1   Using index

It basically says only one row is computed for each of the process.

number of data is 1.6 million, but number of data in a box is only 30

Similar query without spatial index took 62 seconds.

SELECT TB.ID
FROM `tablebusiness` AS TB
JOIN tableauxiliary AS TA ON TA.BusinessID = TB.ID
WHERE TB.Latitude
BETWEEN - 6.1928749092968
AND - 6.1771250907032
AND TB.Longitude
BETWEEN 106.7651250907
AND 106.7808749093
AND MATCH (
TA.FullTextSearch
)
AGAINST (
'res*'
IN BOOLEAN
MODE
)
AND TA.Prominent >15

So yea there are improvements by using spatial. But not as well as I wish.

share|improve this question
    
can you post the EXPLAIN of that query? –  golimar Jun 27 '12 at 13:15
    
You don't have a full text index, do you? –  ypercube Jun 27 '12 at 13:52
    
Dup of dba.stackexchange.com/questions/19998/… –  Rick James Jun 29 '12 at 22:11
    
"Nearest Neighbor" -- an efficient technique: mysql.rjweb.org/doc.php/latlng –  Rick James Jun 29 '12 at 22:13
    
I have fulltext index. –  Jim Thio Aug 3 '12 at 5:20
show 2 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.