Say I want to find 20 closest business near me.
My table structure is like this: BusinessID varchar(250) utf8_unicode_ci No None Browse distinct values Change Drop Primary Unique Index Fulltext Prominent double No None Browse distinct values Change Drop Primary Unique Index Fulltext LatLong point No None Browse distinct values Change Drop Primary Unique Index Fulltext FullTextSearch varchar(600) utf8_bin No None Browse distinct values Change Drop Primary Unique Index Fulltext With selected: Check All / Uncheck All With selected: Print viewPrint view Propose table structurePropose table structureDocumentation Add new fieldAdd field(s) At End of Table At Beginning of Table After Indexes: Documentation Action Keyname Type Unique Packed Field Cardinality Collation Null Comment Edit Drop PRIMARY BTREE Yes No BusinessID 1611454 A Edit Drop Prominent BTREE No No Prominent 0 A Edit Drop LatLong BTREE No No LatLong (25) 0 A Edit Drop sx_mytable_coords SPATIAL No No LatLong (32) 0 A Edit Drop FullTextSearch FULLTEXT No No FullTextSearch 0
There are 1.6 million bizs. Of course it's stupid to compute distance for all of them and then sort it.
That's where geo spatial index kicks in right?
So what SQL comman I need to cast?
- I am using mysql myisam spatial index. However I did not specify this before. So I will accept those who answer it to show my appreciation and ask another question.
- I do not want to compute distance for the whole table
- I do not want to compute distance for any region which are still inefficient
- I do want to compute distance for reasonable number of points because I want to sort the points by distance and be able to display point 1-20, 21-40, 41-60, etc.