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

[Rick James]

Your latest SELECT will ignore all indexes and read the entire table (a "table scan"). Run EXPLAIN SELECT... to get clues to that effect. It will also create a temp table ("Using temporary") and do a sort ("Using filesort").

A SELECT with something like "WHERE lat >= 42 AND lat < 45" would use any index starting with lat, to _some_ advantage.

These will be consecutive entries in an index or table ordered by (lat, lng):

| 32.826 |   70.782 | Sarai Naurang        |
| 32.827 |   35.086 | Qiryat Bialik        |
| 32.833 |  129.850 | Togitsu              |
| 32.834 |  -97.229 | North Richland Hills |
| 32.835 | -116.766 | Alpine               |
| 32.836 |   35.075 | Qiryat Motzkin       |
| 32.837 |  -97.082 | Euless               |
| 32.838 | -116.973 | Santee               |
| 32.841 |  -83.632 | Macon                |
| 32.841 |  120.313 | Dongtai              |

Notice how `lat` is monotonic and `lng` is bouncing around the globe? Having a range on the first field (lat) is useful for localizing the effort withing this list, but having a range on any other fields is useless.

> 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.

The following is meaningful, once punctuation is added:
"That that is is that that is not is not is not that it it is."
I can punctuate my sentence, can you punctuate yours?