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. I think the code speaks for itself ;-) ... I just don't know the right index ...

test.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 idx2 (type, article_id, cnt);

DROP TABLE article;
CREATE TABLE article (id INT, cat_id INT);
ALTER TABLE article ADD INDEX idx3 (id, cat_id);
ALTER TABLE article ADD INDEX idx4 (cat_id);

INSERT INTO article (id, cat_id) VALUES (1, 1);
INSERT INTO article (id, cat_id) VALUES (2, 1);
INSERT INTO article (id, cat_id) VALUES (3, 1);
INSERT INTO article (id, cat_id) VALUES (4, 2);

INSERT INTO stats (article_id, cnt, type) VALUES (1, 9, 1);
INSERT INTO stats (article_id, cnt, type) VALUES (1, 13, 2);
INSERT INTO stats (article_id, cnt, type) VALUES (1, 5, 3);
INSERT INTO stats (article_id, cnt, type) VALUES (1, 3, 4);
INSERT INTO stats (article_id, cnt, type) VALUES (3, 6, 1);

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 where; Using index; Using temporary; Using filesort
-- Using where; Using index