MySQL：在连接列上分组的索引(MySQL: indexes to group by on joined column)

我正在尝试优化我的索引以进行JOIN，然后对来自联接表的其中一列进行GROUP BY。

我正在通过运行下面的脚本进行测试，使用索引，但我似乎无法弄清楚第三个查询需要哪些索引。

已解决：添加虚拟数据使得sql行为不同，我以前尝试的其中一个索引工作得很好！

CREATE DATABASE stats_idx_test; USE stats_idx_test; DROP TABLE stats; CREATE TABLE stats (article_id INT, cnt INT, type INT); ALTER TABLE stats ADD INDEX idxs1 (article_id, cnt, type); DROP TABLE article; CREATE TABLE article (id INT, cat_id INT); ALTER TABLE article ADD INDEX idxa2 (cat_id, id); INSERT INTO article (id, cat_id) VALUES (1, 1); INSERT INTO stats (article_id, cnt, type) VALUES (1, 9, 1); INSERT INTO stats (article_id, cnt, type) VALUES (1, 13, 2); EXPLAIN SELECT SUM(stats.cnt) FROM stats WHERE stats.type = 1 AND stats.article_id = 1; -- Using where; Using index EXPLAIN SELECT article.cat_id, SUM(stats.cnt) FROM stats JOIN article ON (stats.article_id = article.id) WHERE stats.type = 1 AND article.cat_id = 1; -- Using where; Using index EXPLAIN SELECT article.cat_id, SUM(stats.cnt) FROM stats JOIN article ON (stats.article_id = article.id) WHERE stats.type = 1 GROUP BY article.cat_id; -- Using index -- Using where; Using index

I'm trying to optimize my indexes for doing a JOIN and then GROUP BY one of the columns from the joined table.

I'm testing by running the script below, playing with the indexes, but I just can't seem to figure out what indexes I need for the 3rd query.

SOLVED: adding dummy data makes sql behave differently and one of the indexes I previously tried works just fine!

CREATE DATABASE stats_idx_test; USE stats_idx_test; DROP TABLE stats; CREATE TABLE stats (article_id INT, cnt INT, type INT); ALTER TABLE stats ADD INDEX idxs1 (article_id, cnt, type); DROP TABLE article; CREATE TABLE article (id INT, cat_id INT); ALTER TABLE article ADD INDEX idxa2 (cat_id, id); INSERT INTO article (id, cat_id) VALUES (1, 1); INSERT INTO stats (article_id, cnt, type) VALUES (1, 9, 1); INSERT INTO stats (article_id, cnt, type) VALUES (1, 13, 2); EXPLAIN SELECT SUM(stats.cnt) FROM stats WHERE stats.type = 1 AND stats.article_id = 1; -- Using where; Using index EXPLAIN SELECT article.cat_id, SUM(stats.cnt) FROM stats JOIN article ON (stats.article_id = article.id) WHERE stats.type = 1 AND article.cat_id = 1; -- Using where; Using index EXPLAIN SELECT article.cat_id, SUM(stats.cnt) FROM stats JOIN article ON (stats.article_id = article.id) WHERE stats.type = 1 GROUP BY article.cat_id; -- Using index -- Using where; Using index