CODEWITHSUNDEEP
mysqlsqlperformancegreatest-n-per-group
Guillaume
Guillaume

Reputation: 113

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

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:

104506
104498
104511
104501
104552

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!

Upvotes: 1

Views: 132

Answers (2)

challet
challet

Reputation: 972

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).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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).

Upvotes: 2

Related Questions