"J.F. Groff" <jfgroff@gmail.com> wrote:
>
> 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.
>
> The query works fine, but MySQL uses only one index
>
> +----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+
>| 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 |
> +----+-------------+--------+-------+---------------+-------+---------+------+------+-------------+

Seems you don't know of multi-column-indexes nor of the spatial
indexing extension (MyISAM only)

http://dev.mysql.com/doc/refman/5.0/...ate-index.html
http://dev.mysql.com/doc/refman/5.0/...xtensions.html

both would help you much

> I have seen conversations
> mentioning that the MySQL query planner is restricted to using only one
> index per table per query.

This is right and even index merging does not change it. Index merging
will use multiple indexes to retrieve part of the result set using one
index and part of the resultset using another index. But for each
single row of the result set only one index will be used.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/