How do I optimize this query that gets the newest entry of each group?

Question

I have a query, that finds the newest item of each group based on the ID returned by a subquery. The problem is, that the table has a bit over 5000 entries and even with only this amount of entries the query takes almost 5 minutes to complete. I believe it should be possible to do this more efficiently. Can anyone help me do some optimization?

Here is the query in question.

SELECT * 
FROM ast a 
WHERE a.id = ( SELECT id 
               FROM ast 
               WHERE a.tid = tid 
               ORDER BY timestamp DESC LIMIT 1)

The timestamp column is taken from the subquery table. I didn't assign an alias to it.

I am using MariaDB 10.3.39.

Answer 1

You can try the following query.
Adding an index on (tid, timestamp, id) should help with efficiency:

SELECT a.* 
FROM
    ( SELECT DISTINCT tid
      FROM ast
    ) AS t
  LEFT JOIN ast AS a 
    ON a.id = 
       ( SELECT ti.id
         FROM ast AS ti
         WHERE ti.tid = t.tid 
         ORDER BY ti.timestamp DESC
         LIMIT 1
       ) ;

Answer 2

The only efficient solution for the "newest one of the each group" is denormalization.

You have to create the table of the same stucture as table storing your groups but with UNIQUE index on the group ID. Then each time you insert the row in the main table you have to INSERT..ON DUPLICATE KEY UPDATE aka IODKU the same row to the auxiliary table. Due to the IODKU approach that table will store the newest ones for each group only. And you'll be able to fetch them by simple and fast query.

Here "denormalization" mean that each table of the database represents a type. Two tables of the same structure are considered as redundant and should be avoided in theory. But practical reasons force us to violate the relation theory.