Re: Optimised way to search over 2 milllion poi data in mysql

[newbie Shai]

Dear Rick,
Sorry for my punctuation.Yes I tested and found that my new script is full table scan to decide on the one single match. Well below is a sample of my data.

1 Minor Road Jalan Angsiville 5 Seremban N.Sembilan 2.682438 101.999978

2 Highway Lebuhraya Utara Selatan Seremban N.Sembilan 2.627832 101.999981

3 Minor Road Jalan Angsiville 5 Seremban N.Sembilan 2.680936 101.999987

4 Provincial Road Jalan Jelebu Seremban N.Sembilan 2.761409 101.999989

5 Provincial Road Jalan Kuala Pilah Seremban N.Sembilan 2.721865 101.99999

6 Major Road Persiaran Bunga Tanjung Seremban N.Sembilan 2.708489 101.999993

7 Minor Road Jalan Sri Telawi 1 Seremban N.Sembilan 2.728049 101.999994

8 Minor Road Persiaran Sri Telawi 1 Seremban N.Sembilan 2.728049 101.999994

currently we have arranged it by long now. So in this scenario now what is your best advice.

I also saw this in your codes.

"SELECT COUNT(*) INTO @near_ct
FROM cities6
WHERE lat BETWEEN @my_lat - @dlat
AND @my_lat + @dlat -- PARTITION Pruning and bounding box
AND lon BETWEEN @my_lon - @dlon
AND @my_lon + @dlon -- first part of PK
AND ", _condition);

How do you decide on the @dlat and @lon ? I am planning to tweak my new query using this idea of + and - @dlat and @dlon ?