Why is this SQL generating a temporary table and running so slow?

Question

Hi,

I have the following SQL generated from my Rails app, it is trying to get a list of all auto models that have live adverts in a marketplace app & from mysql:

SELECT `models`.* FROM `models` 
  INNER JOIN `autos` ON autos.model_id = models.id 
  INNER JOIN `ads` ON `ads`.id = `autos`.ad_id 
WHERE (ads.ad_status_id = 4 AND pub_start_date < NOW() AND pub_end_date > NOW() AND models.manufacturer_id = 50 ) 
GROUP BY models.id ORDER BY models.name;

When I run an explain, this is what I get:

Id  1   1   1
Select Type SIMPLE  SIMPLE  SIMPLE
Table   models  autos   ads
Type    ref ref eq_ref
Possible Keys   PRIMARY,manufacturer_id model_id,ad_id  PRIMARY,quick_search,ad_status_id
Key manufacturer_id model_id    PRIMARY
Key Length  5   4   4
Ref const   concept_development.models.id   concept_development.autos.ad_id
Rows    70  205 1
Extra   Using where; Using temporary; Using filesort    Using where Using where

I cannot understand why the query is generating a temporary table / using file-sort - all of the referenced keys are indexes. Been trying to figure this out for a few days now and getting nowhere.

Any help is very much appreciated!

EXPLAIN models:

+---------------------+-------------+------+-----+---------+----------------+
| Field               | Type        | Null | Key | Default | Extra          |
+---------------------+-------------+------+-----+---------+----------------+
| id                  | int(11)     | NO   | PRI | NULL    | auto_increment |
| name                | varchar(32) | YES  |     | NULL    |                |
| manufacturer_id     | int(11)     | YES  | MUL | NULL    |                |
| vehicle_category_id | int(11)     | NO   | MUL | 1       |                |
| synonym_names       | longtext    | YES  |     | NULL    |                |
+---------------------+-------------+------+-----+---------+----------------+

SHOW INDEXES FROM models:

+--------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
| models |          0 | PRIMARY             |            1 | id                  | A         |        2261 |     NULL | NULL   |      | BTREE      |         |
| models |          1 | manufacturer_id     |            1 | manufacturer_id     | A         |         205 |     NULL | NULL   | YES  | BTREE      |         |
| models |          1 | vehicle_category_id |            1 | vehicle_category_id | A         |           7 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+

MODEL TABLE STATUS:

+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| models | MyISAM |      10 | Dynamic    | 2261 |             26 |       61000 | 281474976710655 |        84992 |         0 |           2751 | 2010-09-28 18:42:45 | 2010-09-28 18:42:45 | 2010-09-28 18:44:00 | latin1_swedish_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+

EXPLAIN ADS

+------------------+--------------------------+------+-----+---------------------+----------------+
| Field            | Type                     | Null | Key | Default             | Extra          |
+------------------+--------------------------+------+-----+---------------------+----------------+
| id               | int(10)                  | NO   | PRI | NULL                | auto_increment |
| fp_urn           | int(10)                  | NO   | MUL | 0                   |                |
| user_id          | int(10)                  | NO   | MUL | 0                   |                |
| ad_status_id     | int(3) unsigned          | NO   | MUL | 1                   |                |
| style_id         | int(10)                  | NO   |     | 3                   |                |
| search_tags      | varchar(255)             | YES  |     | NULL                |                |
| title            | varchar(255)             | NO   |     |                     |                |
| description      | text                     | YES  |     | NULL                |                |
| currency         | enum('EUR','GBP')        | NO   |     | EUR                 |                |
| price            | decimal(8,2)             | NO   | MUL | 0.00                |                |
| proposal_type    | enum('Offered','Wanted') | NO   |     | Offered             |                |
| category_id      | int(10)                  | YES  |     | 0                   |                |
| contact          | varchar(50)              | NO   | MUL |                     |                |
| area_id          | int(10)                  | NO   |     | 0                   |                |
| origin_id        | int(10)                  | NO   |     | 0                   |                |
| reject_reason_id | int(3)                   | NO   |     | 0                   |                |
| date_created     | timestamp                | NO   |     | 0000-00-00 00:00:00 |                |
| last_modified    | timestamp                | NO   |     | CURRENT_TIMESTAMP   |                |
| pub_start_date   | datetime                 | YES  |     | 0000-00-00 00:00:00 |                |
| pub_end_date     | datetime                 | YES  |     | 0000-00-00 00:00:00 |                |
| bumped_up_date   | datetime                 | YES  |     | 0000-00-00 00:00:00 |                |
| state            | smallint(6)              | YES  |     | NULL                |                |
| eproofed         | tinyint(1)               | NO   |     | 0                   |                |
| is_featured      | int(1)                   | NO   |     | 0                   |                |
| num_featured_imp | int(10)                  | YES  |     | 0                   |                |
| num_direct_imp   | int(10)                  | YES  |     | 0                   |                |
| is_top_listed    | int(1)                   | NO   |     | 0                   |                |
| delta            | tinyint(1)               | NO   |     | 0                   |                |
| ext_ref_id       | varchar(50)              | YES  |     | NULL                |                |
| email_seller     | tinyint(1)               | YES  |     | 1                   |                |
| sort_by          | int(10)                  | YES  |     | 8                   |                |
| permalink        | varchar(500)             | YES  |     | NULL                |                |
| external_url     | varchar(255)             | YES  |     | NULL                |                |
+------------------+--------------------------+------+-----+---------------------+----------------+

SHOW TABLE STATUS FROM concept_development WHERE NAME LIKE 'ads';

+------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-------------------------------------------------+---------+
| Name | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options                                  | Comment |
+------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-------------------------------------------------+---------+
| ads  | InnoDB |      10 | Compact    | 656350 |            232 |   152748032 |               0 |     87736320 | 340787200 |        1148382 | 2010-09-29 09:55:46 | NULL        | NULL       | utf8_general_ci |     NULL | checksum=1 delay_key_write=1 row_format=DYNAMIC |         |
+------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-------------------------------------------------+---------+

SHOW INDEXES FROM ADS

+-------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| ads   |          0 | PRIMARY   |            1 | id           | A         |      521391 |     NULL | NULL   |      | BTREE      |         |
| ads   |          1 | NewIndex1 |            1 | ad_status_id | A         |          15 |     NULL | NULL   |      | BTREE      |         |
| ads   |          1 | NewIndex1 |            2 | pub_end_date | A         |      260695 |     NULL | NULL   | YES  | BTREE      |         |
| ads   |          1 | NewIndex1 |            3 | category_id  | A         |      521391 |     NULL | NULL   | YES  | BTREE      |         |
| ads   |          1 | NewIndex1 |            4 | style_id     | A         |      521391 |     NULL | NULL   |      | BTREE      |         |
| ads   |          1 | NewIndex2 |            1 | user_id      | A         |      130347 |     NULL | NULL   |      | BTREE      |         |
| ads   |          1 | NewIndex3 |            1 | price        | A         |        7667 |     NULL | NULL   |      | BTREE      |         |
| ads   |          1 | contact   |            1 | contact      | A         |      260695 |     NULL | NULL   |      | BTREE      |         |
| ads   |          1 | fp_urn    |            1 | fp_urn       | A         |      521391 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

EXPLAIN autos

+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------+----------------+
| Field             | Type                                                                                                                                                                                                                                                                                                                                 | Null | Key | Default     | Extra          |
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------+----------------+
| id                | int(10)                                                                                                                                                                                                                                                                                                                              | NO   | PRI | NULL        | auto_increment |
| ad_id             | int(10)                                                                                                                                                                                                                                                                                                                              | YES  | MUL | NULL        |                |
| style_id          | int(10)                                                                                                                                                                                                                                                                                                                              | YES  | MUL | NULL        |                |
| manufacturer_id   | int(10)                                                                                                                                                                                                                                                                                                                              | NO   | MUL | NULL        |                |
| model_id          | int(10)                                                                                                                                                                                                                                                                                                                              | NO   | MUL | NULL        |                |
| registration      | varchar(10)                                                                                                                                                                                                                                                                                                                          | YES  |     | NULL        |                |
| year              | int(4)                                                                                                                                                                                                                                                                                                                               | YES  |     | NULL        |                |
| fuel_type         | enum('Petrol','Diesel')                                                                                                                                                                                                                       | NO   |     | Petrol      |                |
| colour            | varchar(75)                                                                                                                                                                                                                                                                                                                          | YES  |     | NULL        |                |
| mileage           | varchar(25)                                                                                                                                                                                                                                                                                                                          | NO   |     | Not Entered |                |
| mileage_units     | enum('mls','kms')                                                                                                                                                                                                                                                                                                                    | NO   |     | mls         |                |
| num_doors         | varchar(25)                                                                                                                                                                                                                                                                                                                          | NO   |     | Not Entered |                |
| num_owners        | int(2)                                                                                                                                                                                                                                                                                                                               | YES  |     | NULL        |                |
| engine_size       | varchar(10)                                                                                                                                                                                                                                                                                                                          | YES  |     | NULL        |                |
| transmission_type | enum('Manual','Automatic')                                                                                                                                                                                                                                                               | NO   |     | Manual      |                |
| body_type         | enum('Saloon','Hatchback')                                                                                                                                                                                                              | NO   |     | Saloon      |                |
| condition         | varchar(75)                                                                                                                                                                                                                                                                                                                          | NO   |     | NA          |                |
| extra_features    | text                                                                                                                                                                                                                                                                                                                                 | YES  |     | NULL        |                |
| tax_expiry        | varchar(7)                                                                                                                                                                                                                                                                                                                           | YES  |     | NULL        |                |
| nct_expiry        | varchar(7)                                                                                                                                                                                                                                                                                                                           | YES  |     | NULL        |                |
| variation         | text                                                                                                                                                                                                                                                                                                                                 | YES  |     | NULL        |                |
| tax_class         | enum('Agricultural','Bus') | NO   |     | Private     |                |
| co2               | int(9)                                                                                                                                                                                                                                                                                                                               | YES  |     | NULL        |                |
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------+----------------+

SHOW TABLE STATUS FROM concept_development WHERE NAME LIKE 'autos'

+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-------------------------------------------------+---------+
| Name  | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options                                  | Comment |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-------------------------------------------------+---------+
| autos | InnoDB |      10 | Compact    | 196168 |            136 |    26804224 |               0 |     26279936 | 340787200 |         485405 | 2010-09-17 22:09:45 | NULL        | NULL       | utf8_general_ci |     NULL | checksum=1 delay_key_write=1 row_format=DYNAMIC |         |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-------------------------------------------------+---------+

show indexes from autos;

+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| autos |          0 | PRIMARY         |            1 | id              | A         |      294937 |     NULL | NULL   |      | BTREE      |         |
| autos |          1 | ad_id           |            1 | ad_id           | A         |      294937 |     NULL | NULL   | YES  | BTREE      |         |
| autos |          1 | style_id        |            1 | style_id        | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |
| autos |          1 | manufacturer_id |            1 | manufacturer_id | A         |         194 |     NULL | NULL   |      | BTREE      |         |
| autos |          1 | model_id        |            1 | model_id        | A         |         830 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+

Answer 1

You have a index on pub_end_date, but not on pub_start_date and your WHERE clause references both.

It looks like it is not using the pub_end_date index, but this could be because it needs to check pub_start_date as well.

Answer 2

From the MySQL documentation:

Temporary tables can be created under conditions such as these:
    * If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

Answer 3

Change all the text columns to varchar. If you need to maintain them as "text", you'll need to snowflake the schema and exclude the description tables from this query.

If any of the columns in any of the tables are text or blob, MySQL automatically creates an on-disk temporary table, rather than an in-memory temporary table. The temporary table itself isn't killing you, it's the fact that it's writing it to the disk.

http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

• Presence of a BLOB or TEXT column in the table

Answer 4

This isn't explaining why but how about you rewrite your query to not use a group by? I think you're just joining on those tables to ensure there exists an ad of interest. So how about:

SELECT `models`.* 
FROM `models` 
WHERE models.manufacturer_id = 50 
AND EXISTS (   SELECT * FROM `autos` 
               INNER JOIN `ads` ON  `ads`.id = `autos`.ad_id 
               WHERE autos.model_id = models.id 
               AND ads.ad_status_id = 4 
               AND ads.pub_start_date < NOW() 
               AND ads.pub_end_date > NOW() 
             )
ORDER BY models.name;

The performance issues might be related to the group by, in which case this would improve performance.

Maybe it'd look a bit nicer with and NOW() between ads.pub_start_date and ads.pub_end_date, if you're allowed to do that in mysql (and if it works how you want with the edge cases).