我有一张简单的桌子
stock_ledger_id INT(10) (Primary)
piece_to_bin_id INT(10)
quantity INT(11)
create_datetime TIMESTAMP
... and a few VARCHARs
一些简单的索引
Key_name Cardinality
PRIMARY 1510443
piece_to_bin_id 100696
这个相当简单的查询大约需要8秒钟:
SELECT piece_to_bin_id,
SUM(quantity),
MAX(create_datetime)
FROM stock_ledger
GROUP BY piece_to_bin_id
这是EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE stock_ledger ALL NULL NULL NULL NULL 1512976 Using temporary; Using filesort
我发现通过强制索引可以将它降低到约0.5秒:
SELECT piece_to_bin_id,
SUM(quantity),
MAX(create_datetime)
FROM stock_ledger
FORCE INDEX (piece_to_bin_id)
GROUP BY piece_to_bin_id
然后EXPLAIN看起来像这样:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE stock_ledger index NULL piece_to_bin_id 4 NULL 1512976
我使用MySQL 5.1.41,表是MyISAM,我之前运行过ANALYZE TABLE.
所以我坚持“MySQL再次出错,只是强制索引”或者是否存在MySQL使用全表扫描的实际原因?也许我能解决一个问题?