我有把握地确定答案是否在于拥有不同的索引。我有一个查询速度过慢,但是只有当它采用以下完整形式时,如果我删除查询的某些部分却很快,那么如何才能使它更好呢?
慢:
SELECT json
FROM requests
WHERE spider = 'foo'
AND load_count = ( SELECT MIN( load_count ) FROM requests )
AND load_count < 50
LIMIT 500;
说明:
+----+-------------+----------+------+-------------------------+--------------+---------+-------+--------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+-------------------------+--------------+---------+-------+--------+------------------------------+
| 1 | PRIMARY | requests | ref | load_count,spider_index | spider_index | 90 | const | 200845 | Using where |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+----------+------+-------------------------+--------------+---------+-------+--------+------------------------------+
数据库结构:
CREATE TABLE `requests` (
`added` int(11) NOT NULL AUTO_INCREMENT,
`url` char(255) NOT NULL,
`spider` char(30) NOT NULL,
`referer` char(255) DEFAULT NULL,
`json` text NOT NULL,
`load_count` int(11) NOT NULL DEFAULT '0',
`processed` tinyint(1) NOT NULL DEFAULT '0',
`invalid` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`added`),
UNIQUE KEY `url` (`url`),
KEY `load_count` (`load_count`),
KEY `spider_index` (`spider`)
) ENGINE=MyISAM AUTO_INCREMENT=5285840 DEFAULT CHARSET=utf8
在像Neo建议更新索引后,我得到了巨大的改进:
+----+-------------+----------+------+-------------------+-------------------+---------+-------------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+-------------------+-------------------+---------+-------------+------+------------------------------+
| 1 | PRIMARY | requests | ref | spider_load_count | spider_load_count | 94 | const,const | 1487 | Using where |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+----------+------+-------------------+-------------------+---------+-------------+------+------------------------------+
最佳答案
alter table requests drop index load_count;
alter table requests drop index spider_index;
alter table requests add index spider_load_count(load_count, spider);
关于mysql - 在MIN列值和CHAR列上的SELECT在MySQL中确实很慢,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/5578308/