这是我的查询:
CREATE TEMPORARY TABLE temptbl (
pibn INT UNSIGNED NOT NULL, page SMALLINT UNSIGNED NOT NULL)
ENGINE=MEMORY;
INSERT INTO temptbl (
SELECT pibn,page FROM mytable
WHERE word1=429907 AND word2=0);
ALTER TABLE temptbl ADD INDEX (pibn,page);
SELECT word1,COUNT(*) AS aaa
FROM mytable a
INNER JOIN temptbl b
ON a.pibn=b.pibn AND a.page=b.page
WHERE word2=0
GROUP BY word1 ORDER BY aaa DESC LIMIT 10;
DROP TABLE temptbl;
问题在于
SELECT word1,COUNT(*) AS aaa
,具体来说是COUNT函数。这个SELECT语句需要16秒的时间。EXPLAIN显示:
+----+-------------+-------+------+---------------------------------+-------------+---------+-------------------------------------------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------------+-------------+---------+-------------------------------------------------------------+-------+---------------------------------+
| 1 | SIMPLE | b | ALL | pibn | NULL | NULL | NULL | 26778 | Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | w2pibnpage1,word21pibn,pibnpage | w2pibnpage1 | 9 | const,db.b.pibn,db.b.page | 4 | Using index |
+----+-------------+-------+------+---------------------------------+-------------+---------+-------------------------------------------------------------+-------+---------------------------------+
使用的索引(w2pibnpage1)位于以下位置:
word2,pibn,page,word1,id
我已经为此苦苦挣扎了数天,尝试了不同的索引列(这很烦人,因为重建索引需要一个小时 - 数据量有数百万行)。
我的索引应该是什么样的,或者我应该怎么做才能使这个查询在几秒钟内运行(正常情况下应该如此)?
EXPLAIN
。由于表是空的,更改索引或运行EXPLAIN
将非常快速。 - Jocelyn