query not using index(es)

[pedalpete]

I'm running a few mysql queries, and noticed some really slow behavior.

I ran the explain, and on the first query, only one index is being used when there should be two, and in the second query no index is being used.

Here are the explain outputs.

mysql> EXPLAIN SELECT  `lat` ,  `long`  FROM updateshows WHERE city =  'Philladelphia' AND state =  'Pennsylvania' LIMIT 0 , 1;
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | updateshows | ALL  | NULL          | NULL | NULL    | NULL | 1581987 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT  `lat` ,  `long`  FROM updateshows WHERE city =  'Philladelphia' AND state =  'Pennsylvania' AND lat !=0 LIMIT 0 , 1;
+----+-------------+-------------+-------+---------------+--------------+---------+------+---------+-------------+
| id | select_type | table       | type  | possible_keys | key          | key_len | ref  | rows    | Extra       |
+----+-------------+-------------+-------+---------------+--------------+---------+------+---------+-------------+
|  1 | SIMPLE      | updateshows | range | getshows_idx  | getshows_idx | 4       | NULL | 1125490 | Using where |
+----+-------------+-------------+-------+---------------+--------------+---------+------+---------+-------------+
1 row in set (1.37 sec)

the indexes I expected would be used are
location_idx - which indexes address city state
getshows_idx which indexes lat, long date

I understand why the second search would use the getshows_idx, but thought both should use location_idx.
Is that not correct?

[coolsti]

Your indexes are not being used because the indexed column does not appear in the where clauses. Only in the second query do you have your lat index appearing in the where clause.

Try adding indexes on some or all of the columns in the where clauses and run Explain again. You should notice a huge speedup in your query.

[pedalpete]

Thanks Coolsti,

buy city and state are in my where clause, and they are also in my location_idx.
It is my understanding that the indexes don't have to perfectly match the where requests, or else I would have a ton of indexes for each different query.
Is that not correct?

I would expect mysql to use a combination of location_idx and getshows_idx, rather than my needing to create one or two new indexes containing data which is already in the indexes.

I have 5 indexes on this table already. How many indexes is too many?

[coolsti]

From my understanding, an index is a search tree created as an additional table. If you are doing a query with "Where A = 'abc' " then an index on A would make a search tree based on the - not necessarily unique - values of A. This tells MySQL exactly where in the main table to search for the rows that are to be selected.

You can then imagine that an index on another attribute, for example the table's primary key, would not at all help if the index on A is not present. There is no information available in the primary key or other index that will help in locating certain values of A.

Likewise, if your query had "Where A = 'abc' and B = 'def' " I would imagine an index alone on A or alone on B would help the situation over not having any index at all. I am not sure how much help having two indexes, one on A and one on B will work, but I can only imagine it would help even more. And for this particular case, I would suppose one index on both A and B would be optimal, as this tells MySQL exactly which rows match this query. But again, you don't want to have too many indexes, so you should just add the indexes that are needed to get you the performance you need.

Take all this with a grain of salt, I am no expert here. Might be a good idea to read up on MySQL indexing and what it does for you (and for me but I haven't the time now).