List:General Discussion« Previous MessageNext Message »
From:Scott Gifford Date:April 21 2005 3:31pm
Subject:Slow query: mysql not using correct indexes?
View as plain text  
Hello,

I'm having a problem with query running very slowly.  I run similar
queries on other tables all the time that perform as expected, and
this query used to run fine until I removed an explicit LEFT JOIN and
let the optimizer decide in what order to join two of the tables.
That fixed some other performance problems I was having, but seems to
have introduced this new one.

I'm using "Ver 11.16 Distrib 3.23.49, for pc-linux-gnu (i686)" on an
older copy of RedHat Linux 7.3 (kernel 2.4.25, libc6 2.2.5).

I've simplified the problem as much as I can and still reproduce it;
the actual tables I'm interested in are much larger, with many more
columns and rows.

The basic problem seems to be when I do a query sorting by price and
joining these three tables together, mysql resorts to "Using
temporary; Using filesort":

    mysql> EXPLAIN SELECT test_homes.price, 
                          test_homes.mls_num,
                          test_homes_supplemental.bathrooms,
                          test_homes_stats.detail_views
                     FROM test_homes, 
                          test_homes_supplemental 
                LEFT JOIN test_homes_stats 
                          ON test_homes.mls_num = test_homes_stats.mls_num 
                    WHERE test_homes.mls_num = test_homes_supplemental.mls_num 
                 ORDER BY test_homes.price
                    LIMIT 10;
    
   
+-------------------------+--------+---------------+---------+---------+---------------------------------+------+---------------------------------+
    | table                   | type   | possible_keys | key     | key_len | ref          
                  | rows | Extra                       |
   
+-------------------------+--------+---------------+---------+---------+---------------------------------+------+---------------------------------+
    | test_homes_supplemental | ALL    | PRIMARY       | NULL    |    NULL | NULL         
                  |  100 | Using temporary; Using filesort |
    | test_homes              | eq_ref | PRIMARY       | PRIMARY |       9 |
test_homes_supplemental.mls_num |    1 |                       |
    | test_homes_stats        | eq_ref | PRIMARY       | PRIMARY |       9 |
test_homes.mls_num              |    1 |                       |
   
+-------------------------+--------+---------------+---------+---------+---------------------------------+------+---------------------------------+

That's very slow for 22,000 rows.  I don't know why it's doing this,
since the column I'm sorting by is indexed; it seems like it should
get the data from the price index on test_homes, then use eq_ref to
join in test_homes_supplemental and test_homes_stats.  Here are the
table definitions:

    CREATE TABLE `test_homes` (
      `mls_num` char(9) NOT NULL default '',
      `price` mediumint(8) unsigned default NULL,
        PRIMARY KEY  (`mls_num`),
      KEY `price` (`price`));
    
    CREATE TABLE `test_homes_supplemental` (
      `mls_num` char(9) NOT NULL default '',
      `bathrooms` tinyint(3) unsigned default NULL,
        PRIMARY KEY  (`mls_num`));
    
     CREATE TABLE `test_homes_stats` (
      `mls_num` char(9) NOT NULL default '',
      `detail_views` int(11) NOT NULL default '0',
        PRIMARY KEY  (`mls_num`));

Both test_homes and test_homes_supplemental contain one row for each
item; test_homes_stats contain zero or one rows for each item.

If I do explicit LEFT JOINs to tell MySQL what order to join in, I get
the results I expect:

    mysql> EXPLAIN SELECT test_homes.price,
                          test_homes.mls_num,
                          test_homes_supplemental.bathrooms,
                          test_homes_stats.detail_views
                     FROM test_homes
                LEFT JOIN test_homes_supplemental 
                          ON test_homes.mls_num = test_homes_supplemental.mls_num
                LEFT JOIN test_homes_stats 
                          ON test_homes.mls_num = test_homes_stats.mls_num 
                 ORDER BY test_homes.price
                    LIMIT 10;
   
+-------------------------+--------+---------------+---------+---------+--------------------+------+-------+
    | table                   | type   | possible_keys | key     | key_len | ref          
     | rows | Extra |
   
+-------------------------+--------+---------------+---------+---------+--------------------+------+-------+
    | test_homes              | index  | NULL          | price   |       4 | NULL         
     |  100 |       |
    | test_homes_supplemental | eq_ref | PRIMARY       | PRIMARY |       9 |
test_homes.mls_num |    1 |       |
    | test_homes_stats        | eq_ref | PRIMARY       | PRIMARY |       9 |
test_homes.mls_num |    1 |       |
   
+-------------------------+--------+---------------+---------+---------+--------------------+------+-------+

But this query is part of a larger query-generating framework, and I'd
rather not fill the framework will special cases; I'm not even sure
how I would identify that this query was a case that needs to be
handled specially.

I've put some sample data here:

    http://www.flinthomes.net/~sgifford/test_homes.txt

I can reproduce the problem exactly with this data.

----ScottG.
Thread
Slow query: mysql not using correct indexes?Scott Gifford21 Apr
  • Re: Slow query: mysql not using correct indexes?Dan Nelson21 Apr