伙计们，我已经尝试了一段时间了，但我无法优化 Using temporary；使用我的 EXPLAIN 查询中的文件排序。 好的，我有这个查询:

 SELECT a.adid, a.adtitle, a.addesc, scat.subcatname
FROM ads a
INNER JOIN cities ct ON a.cityid = ct.cityid
INNER JOIN subcats scat ON a.subcatid = scat.subcatid
INNER JOIN cats cat ON scat.catid = cat.catid
WHERE a.enabled = '1'
AND a.verified ='1'
GROUP BY a.adid
ORDER BY a.createdon DESC
LIMIT 16;

当我执行 EXPLAIN ... 我得到这个:

    EXPLAIN SELECT a.adid, a.adtitle, a.addesc, scat.subcatname 
FROM ads a 
INNER JOIN cities ct ON a.cityid = ct.cityid 
INNER JOIN subcats scat ON a.subcatid = scat.subcatid 
INNER JOIN cats cat ON scat.catid = cat.catid 
WHERE a.enabled = '1' 
AND a.verified ='1' 
GROUP BY a.adid 
ORDER BY a.createdon DESC LIMIT 16;

+----+-------------+-------+--------+----------------------------------+----------+---------+--------------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys                    | key      | key_len | ref                            | rows | Extra                           |
+----+-------------+-------+--------+----------------------------------+----------+---------+--------------------------------+------+---------------------------------+
|  1 | SIMPLE      | cat   | system | PRIMARY                          | NULL     | NULL    | NULL                           |    1 | Using temporary; Using filesort | 
|  1 | SIMPLE      | scat  | ref    | PRIMARY,catid                    | catid    | 2       | const                          |    7 |                                 | 
|  1 | SIMPLE      | a     | ref    | subcatid,cityid,verified,enabled | subcatid | 2       | bakecai_incontri.scat.subcatid |  954 | Using where                     | 
|  1 | SIMPLE      | ct    | eq_ref | PRIMARY                          | PRIMARY  | 2       | bakecai_incontri.a.cityid      |    1 | Using index                     | 
+----+-------------+-------+--------+----------------------------------+----------+---------+--------------------------------+------+---------------------------------+
4 rows in set (0.00 sec)

我尝试了一些 KEY 但没有结果:例如，我为 createdon 和 adid 创建了一个索引，但 mysql 不会使用它。

这是我的表的键:

SHOW INDEX 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 | adid        | A         |        8592 |     NULL | NULL   |      | BTREE      |         | 
| ads |          1 | subcatid           |            1 | subcatid    | A         |           9 |     NULL | NULL   |      | BTREE      |         | 
| ads |          1 | cityid             |            1 | cityid      | A         |         103 |     NULL | NULL   |      | BTREE      |         | 
| ads |          1 | verified           |            1 | verified    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| ads |          1 | enabled            |            1 | enabled     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| ads |          1 | idx_createdon_adid |            1 | createdon   | A         |        8592 |     NULL | NULL   |      | BTREE      |         | 
| ads |          1 | idx_createdon_adid |            2 | adid        | A         |        8592 |     NULL | NULL   |      | BTREE      |         | 
| ads |          1 | srch_text          |            1 | adtitle     | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         | 
| ads |          1 | srch_text          |            2 | addesc      | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         | 
| ads |          1 | srch_text          |            3 | nome        | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         | 
+-------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
10 rows in set (0.00 sec)

这里有一些表格的简要说明:

 DESCRIBE ads
    -> ;
+-----------+----------------------+------+-----+---------------------+----------------+
| Field     | Type                 | Null | Key | Default             | Extra          |
+-----------+----------------------+------+-----+---------------------+----------------+
| adid      | int(10) unsigned     | NO   | PRI | NULL                | auto_increment | 
| nome      | varchar(255)         | YES  |     | NULL                |                | 
| eta       | varchar(255)         | YES  |     | NULL                |                | 
| adtitle   | varchar(100)         | YES  | MUL | NULL                |                | 
| addesc    | text                 | YES  |     | NULL                |                | 
| email     | varchar(50)          | YES  |     | NULL                |                | 
| phone     | varchar(255)         | YES  |     | NULL                |                | 
| showemail | enum('0','1','2')    | YES  |     | NULL                |                | 
| code      | varchar(35)          | YES  |     | NULL                |                | 
| first     | varchar(255)         | YES  |     | NULL                |                | 
| cityid    | smallint(5) unsigned | NO   | MUL | 0                   |                | 
| subcatid  | smallint(5) unsigned | NO   | MUL | 0                   |                | 
| price     | decimal(10,2)        | NO   |     | 0.00                |                | 
| hits      | int(10) unsigned     | NO   |     | 0                   |                | 
| ip        | varchar(15)          | YES  |     | NULL                |                | 
| link      | varchar(255)         | YES  |     | http://             |                | 
| verified  | enum('0','1')        | YES  | MUL | NULL                |                | 
| abused    | int(10) unsigned     | NO   |     | 0                   |                | 
| enabled   | enum('0','1')        | YES  | MUL | NULL                |                | 
| createdon | datetime             | NO   | MUL | 0000-00-00 00:00:00 |                | 
| expireson | datetime             | NO   |     | 0000-00-00 00:00:00 |                | 
| timestamp | timestamp            | NO   |     | CURRENT_TIMESTAMP   |                | 
+-----------+----------------------+------+-----+---------------------+----------------+
22 rows in set (0.02 sec)

DESCRIBE cities
    -> ;
+-----------+----------------------+------+-----+-------------------+----------------+
| Field     | Type                 | Null | Key | Default           | Extra          |
+-----------+----------------------+------+-----+-------------------+----------------+
| cityid    | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment | 
| cityname  | varchar(50)          | YES  |     | NULL              |                | 
| regionid  | smallint(5) unsigned | NO   | MUL | 0                 |                | 
| pos       | smallint(5) unsigned | NO   | MUL | 0                 |                | 
| enabled   | enum('0','1')        | YES  | MUL | NULL              |                | 
| timestamp | timestamp            | NO   |     | CURRENT_TIMESTAMP |                | 
+-----------+----------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)

DESCRIBE cats
    -> ;
+-----------+----------------------+------+-----+-------------------+----------------+
| Field     | Type                 | Null | Key | Default           | Extra          |
+-----------+----------------------+------+-----+-------------------+----------------+
| catid     | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment | 
| catname   | varchar(50)          | YES  |     | NULL              |                | 
| pos       | smallint(5) unsigned | NO   |     | 0                 |                | 
| enabled   | enum('0','1')        | YES  | MUL | NULL              |                | 
| timestamp | timestamp            | NO   |     | CURRENT_TIMESTAMP |                | 
+-----------+----------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

任何帮助，我很感激!

最佳答案

SELECT  a.adid, a.adtitle, a.addesc, scat.subcatname
FROM    ads a
INNER JOIN
        subcats scat
ON      a.subcatid = scat.subcatid
WHERE   a.enabled = '1'
        AND a.verified ='1'
ORDER BY a.createdon
        DESC
LIMIT 16

删除了不需要的联接并摆脱了不需要的 GROUP BY。

要摆脱 filesort，请创建以下索引:

CREATE INDEX ix_ads_e_v_created ON ads (enabled, verified, createdon)

关于MYSQL优化使用临时；使用文件排序!!! -> 帮助，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/4747972/