使用MYSQL进行临时优化;使用filesort !!! - >帮助
我想尝试一段时间的伙计们,我无法设法优化使用临时;从我的EXPLAIN查询中使用filesort。 行,所以,我有这个疑问:使用MYSQL进行临时优化;使用filesort !!! - >帮助
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 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)
我已经尝试了一些关键的,但没有结果:比如我创建索引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)
你需要玩弄迫使指数 - here you will find more information。然而在你的情况下,它并没有什么帮助,因为你使用不同的索引来进行连接和组合,所以mysql没有机会“重用”它们。
OK,它改善,但现在查询扫描所有的行: --hmm我想这是因为所有的线已经启用并验证等于1 ......从什么
EXPLAIN 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;
+----+-------------+-------+--------+----------------------------------------------+--------------------+---------+-----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------------------------------+--------------------+---------+-----------------------------+------+-------------+
| 1 | SIMPLE | a | ref | subcatid,verified,enabled,ix_ads_e_v_created | ix_ads_e_v_created | 4 | const,const | 8485 | Using where |
| 1 | SIMPLE | scat | eq_ref | PRIMARY | PRIMARY | 2 | bakecai_incontri.a.subcatid | 1 | |
+----+-------------+-------+--------+----------------------------------------------+--------------------+---------+-----------------------------+------+-------------+
2 rows in set (0.00 sec)
我知道我必须编制索引吗?但在哪些列/列?
查询没有扫描所有行:它正在使用具有ref参数的索引并输出索引中的记录订单,没有`filesort`。此外,请更新您的原始问题,不要将更新发布为答案。谢谢。 – Quassnoi 2011-01-20 14:38:45
只是为了笑容......改变
SELECT STRAIGHT_JOIN(查询的其余部分)
通过让子类表是小,优化引擎将尝试使用它作为查询和NOT的基础查询中的第一个表...这一个关键词“STRAIGHT_JOIN”帮助我在过去30多个小时内将超过14百万条记录加入到15个查找表中,以将这个过程挂起,持续时间不超过2小时去完成。
确定它改进了,但是...我还有一个小问题,现在查询正在扫描所有行 – user583012 2011-01-20 14:26:34