What's the best query to get last commented articles?

Question

I have a table comment like :

id  article_id    approved  created_at
7   104506        1         2019-12-02 09:36:32
6   104452        0         2019-12-02 09:31:35   
5   104498        1         2019-12-02 09:27:56
4   104506        1         2019-12-02 09:05:45
3   104511        1         2019-12-02 08:58:56
2   104501        1         2019-12-02 08:52:57
1   104552        1         2019-12-02 08:50:10

What I would like:

and I would like to get a distinct list of article_id approved order by created_at desc

So I just do this: SELECT article_id FROM comment WHERE approved = 1 GROUP BY article_id ORDER BY created_at DESC LIMIT 30

But on a 400k rows table, it takes 1,28 secs.

Any idea to have a faster query? Thanks!

Can you share the search terms you used in Google when you were researching this issue? — dfundako, Dec 02 '19 at 22:24
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Dec 02 '19 at 22:27
I believe this is the common [tag:greatest-n-per-group] problem. There are many similar questions on Stack Overflow with good answers. Follow that tag to find them. — Bill Karwin, Dec 02 '19 at 22:45
You have a contradiction -- 104506 was created at two different times. Which time should be used?? — Rick James, Jan 05 '20 at 04:33

score 2 · Accepted Answer · answered Dec 02 '19 at 22:24

2

If I understand correctly, you want to write the query like this:

SELECT article_id
FROM comment
WHERE approved = 1
GROUP BY article_id
ORDER BY MAX(created_at) DESC
LIMIT 30;

For this, you want an index on comment(approved, article_id, created_at).

answered Dec 02 '19 at 22:24

Gordon Linoff

1,242,037
58
646
786

You're right, I had 3 distinct index on approved, article_id and created_at. And creating 1 index with this 3 fields reduce query time from 1,28 secs to 100 ms. Thanks! – Guillaume Dec 02 '19 at 22:44

challet · Answer 2 · 2019-12-02T23:27:12.267

In order to check how the existing indexes behave you can run an EXPLAIN query :

EXPLAIN
SELECT article_id
FROM comment
WHERE approved = 1
GROUP BY article_id
ORDER BY MAX(created_at) DESC
LIMIT 30;

It will tell which indexes are used, how they are used, and also which WHERE conditions, ORDER sorts, JOIN conditions (and maybe others) are in need of indexing. So you can try adding a new one that will fill all of them.

a warning though : each new index will slow down the writing, so better to aim straight.
but one index can be used for various queries : in a 3 fields indexes for instance, it will be used for queries that need indexing on fields (1), (1, 2) or (1, 2, 3) but not (2), (3), (1, 3) or (2, 3).

For a broader inspection of an app or a website about indexes, there is a log_queries_not_using_indexes configuration flag that can be enabled (not to be used in production).

What's the best query to get last commented articles?

2 Answers2