Slow Query Log - Rows Examined over 10 million, EXPLAIN shows under 10,000 - Why so high?

Question

I have a database I'm working on that has some queries showing up in the slow query log.

There are 2 tables:

table1 is a table of businesses with standard info: name, phone, address, city, state, zip, etc. There is also a field for category. There are millions and millions of rows in this table.

table2 is a table of categories. There are only a couple of hundred rows.

The query in question is below:

# Query_time: 20.446852  Lock_time: 0.000044 Rows_sent: 20  Rows_examined: 11410654
use my_database;
SET timestamp=1331074576;
SELECT table1.id, name, phone, address, city, state, zip 
FROM table1 
INNER JOIN table2 ON table2.label=table1.category 
WHERE state = 'tx' and city = 'San Antonio' 
and category.label LIKE 'Health Care & Medical%' group by table1.id limit 0,20;

An EXTENDED EXPLAIN on the query looks like this:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   SIMPLE  table1  index   indx_state,indx_city,index_category,cat_keywords    PRIMARY     4   NULL    5465    946.92  Using where
1   SIMPLE  table2  ref     category_label  category_label  602     my_table.table1.category    1   100.00  Using where; Using index

Here's the problem: This query is taking 20 seconds to run, showing in the slow query log and takes forever to load the html page.

The total records in table1 are over 10 million records, but 'San Antonio' only has 70,000 records. The total records matching the query (ignoring the limit) is only a couple of thousand. Indexes are set up on everything and the EXPLAIN appears to reflect this fact.

Why are the rows examined showing 11 million?

I feel this must be part of the reason the query is dragging so much.

Thanks as always....

Answer 1

I did follow some advice on this post and create an index on city,state. It didn't help my performance really, but another thing ended up helping. Quite possibly the fix I found would also have been more effective by putting an index on both columns.

The solution however, was to add the USE INDEX:

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

By defining which index to use, the query time was reduced from 30 seconds to 1.5 seconds.

I don't know why it worked, but it did.