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 indexI'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最满意答案
对于该组,您需要一个cat_id索引。 您无法应用当前索引。
分组和索引
您还应该考虑将id作为主键。
adding dummy data makes sql behave differently and one of the indexes I previously tried works just fine!
更多推荐
发布评论