title: Slow Query Log - Rows Examined over 10 million, EXPLAIN shows under 10,000 - Why so high?
body: <p>I have a database I'm working on that has some queries showing up in the slow query log.</p>
  
  <p>There are 2 tables:  </p>
  
  <p>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.</p>
  
  <p>table2 is a table of categories.  There are only a couple of hundred rows.</p>
  
  <p>The query in question is below:</p>
  
  <pre><code># 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 &amp; Medical%' group by table1.id limit 0,20;
  </code></pre>
  
  <p>An EXTENDED EXPLAIN on the query looks like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Why are the rows examined showing 11 million?</p>
  
  <p>I feel this must be part of the reason the query is dragging so much.</p>
  
  <p>Thanks as always....</p>