Hello group,

I have searched everywhere and cannot find a solution, I hope your
collective wisdom can help.

My database has a Places table with a few million records, indexed on
latitude and longitude. I want to search for places close to a
particular point. Easy:

SELECT id, name, lat, lon FROM Places
WHERE lat > 55.5 AND lat < 55.7
AND lon > 12.0 AND lon < 12.1;

The query works fine, but MySQL uses only one index, so it takes about
one second instead of a few milliseconds as it should. There is no way
I can put this in production with thousands of users... The EXPLAIN
say:

+----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | Places | range | ixlat,ixlon | ixlon | 6 |
NULL | 4299 | Using where |
+----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+

Same behaviour with MyISAM and InnoDB. I have seen conversations
mentioning that the MySQL query planner is restricted to using only one
index per table per query. This sounds like a strange limitation to
have, but I guess we have no choice. As a workaround, maybe there is a
way to rewrite this with a subquery, but I have not found a convincing
syntax which brings the expected performance gain.

The new Index Merge optimization sounds promising, but it does not seem
to help in my case, as the documentation says:
"If a range scan is possible on some key, an Index Merge is not
considered"
so in my case the range scan has priority, and even if I use FORCE
INDEX (ixlat, ixlon), the query planner still chooses to perform only
one range scan, either on ixlat or on ixlon. I want it to perform the
range scan on both indexes, and then give me the intersection of
results.

Any help will be greatly appreciated. Thanks!

JFG