StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy is this SQL generating a temporary table and running so slow?
primarykey
Id
3936568
data
AcceptedAnswerId
0
AnswerCount
4
ClosedDate
CommentCount
14
CommunityOwnedDate
CreationDate
2010-10-14T18:57:43.193
FavoriteCount
1
LastActivityDate
2010-10-24T15:02:08.297
LastEditDate
2010-10-14T20:05:58.660
LastEditorUserId
220274
OwnerUserId
220274
ParentId
0
PostTypeId
1
Score
3
ViewCount
460
LastEditorDisplayName
text
Body
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: <pre><code>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; </code></pre> When I run an explain, this is what I get: <pre><code>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 </code></pre> 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: <pre><code>+---------------------+-------------+------+-----+---------+----------------+ | 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 | | +---------------------+-------------+------+-----+---------+----------------+ </code></pre> SHOW INDEXES FROM models: <pre><code>+--------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+ | 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 | | +--------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+ </code></pre> MODEL TABLE STATUS: <pre><code>+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | 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 | | | +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ </code></pre> EXPLAIN ADS <pre><code>+------------------+--------------------------+------+-----+---------------------+----------------+ | 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 | | +------------------+--------------------------+------+-----+---------------------+----------------+ </code></pre> SHOW TABLE STATUS FROM concept_development WHERE NAME LIKE 'ads'; <pre><code>+------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-------------------------------------------------+---------+ | 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 | | +------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-------------------------------------------------+---------+ </code></pre> SHOW INDEXES FROM ADS <pre><code>+-------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+ | 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 | | +-------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+ </code></pre> EXPLAIN autos <pre><code>+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------+----------------+ | 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 | | +-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------+----------------+ </code></pre> SHOW TABLE STATUS FROM concept_development WHERE NAME LIKE 'autos' <pre><code>+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-------------------------------------------------+---------+ | 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 | | +-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-------------------------------------------------+---------+ </code></pre> show indexes from autos; <pre><code>+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | 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 | | +-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ </code></pre>
Tags
<sql><mysql><ruby-on-rails><activerecord>
Title
Why is this SQL generating a temporary table and running so slow?
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USJason
UserOwnerUserId
1. USJason
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POWhy is this SQL generating a temporary table and running so slow?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 POWhy is this SQL generating a temporary table and running so slow?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUndeletion
3. VO
 singulars
 PostPostId
 POWhy is this SQL generating a temporary table and running so slow?
 UserUserId
 USJason
 VoteTypeVoteTypeId
 VTBountyStart
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.