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:
我有以下SQL生成从我的Rails应用，它试图得到一个列表，所有的汽车模型都有实时广告在市场应用&从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';
从concept_development显示表状态，例如“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'
从concept_development显示表状态，比如“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 | |
+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
4 个解决方案
#1
2
From the MySQL documentation:
从MySQL文档:
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
http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
#2
2
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.
将所有文本列更改为varchar。如果需要将它们作为“文本”进行维护，则需要对模式进行雪花处理，并从该查询中排除描述表。
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.
如果任何表中的任何列都是文本或blob, MySQL会自动创建磁盘上的临时表，而不是内存中的临时表。临时表本身并没有杀死您，而是将其写入磁盘。
http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
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
- 在表中显示BLOB或文本列
#3
0
You have a index on pub_end_date, but not on pub_start_date and your WHERE clause references both.
在pub_end_date上有一个索引，但是在pub_start_date和WHERE子句中没有引用。
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.
看起来它没有使用pub_end_date索引，但是这可能是因为它也需要检查pub_start_date。
#4
0
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:
这并不是在解释为什么要这样做，但您可以将查询重写为不使用group by吗?我认为你只是加入这些表格，以确保存在感兴趣的广告。所以如何:
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).
如果允许在mysql中使用and NOW()，那么它在ads.pub_start_date和ads.pub_end_date之间看起来可能会更好一些(如果它在边缘情况下可以正常工作的话)。
#1
2
From the MySQL documentation:
从MySQL文档:
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
http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
#2
2
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.
将所有文本列更改为varchar。如果需要将它们作为“文本”进行维护，则需要对模式进行雪花处理，并从该查询中排除描述表。
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.
如果任何表中的任何列都是文本或blob, MySQL会自动创建磁盘上的临时表，而不是内存中的临时表。临时表本身并没有杀死您，而是将其写入磁盘。
http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
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
- 在表中显示BLOB或文本列
#3
0
You have a index on pub_end_date, but not on pub_start_date and your WHERE clause references both.
在pub_end_date上有一个索引，但是在pub_start_date和WHERE子句中没有引用。
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.
看起来它没有使用pub_end_date索引，但是这可能是因为它也需要检查pub_start_date。
#4
0
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:
这并不是在解释为什么要这样做，但您可以将查询重写为不使用group by吗?我认为你只是加入这些表格，以确保存在感兴趣的广告。所以如何:
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).
如果允许在mysql中使用and NOW()，那么它在ads.pub_start_date和ads.pub_end_date之间看起来可能会更好一些(如果它在边缘情况下可以正常工作的话)。