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

[newbie Shai]

Dear Rick,
Sorry I took some time to go and read about the Index BTree where I find it actually like an anchor and then like to the full data with the node is that correct. This part "In the case of INDEX(lat, long), it means that rows in the Index BTree (not in the Data BTree -- keep in mind that the data is in one BTree with the PRIMARY KEY; each secondary index is in a separate BTree) with similar North-South values of latitude will be near each other, and have some chance of being in the same block. (The fields after the first in the INDEX(...) are mostly irrelevant.)
" if I understand carefully you are indexing both lat and long then which is be the node in this case ?

Another thing I found this formula which I think is better then the previous one using the where concept ?

select *,(3958*3.1415926*SQRT(("+Double.toString(latDouble)+"-poi.long)*("+Double.toString(latDouble)+"-poi.long) + COS("+Double.toString(latDouble)+"/57.29578)*COS(poi.long/57.29578)*("+Double.toString(longDouble)+"- poi.lat)*("+Double.toString(longDouble)+"- poi.lat))/180) as distance from poi ORDER BY distance LIMIT 1"

What is you suggestion on this? Will my current indexing work with this?