How to select the last 5 records of each category with one query in MySQL?

Question

MySQL

Hello.
Let's say we have a table with the following structure: id, cid, title.
Is there some elegant way to select the last 5 entries of each category (cid) in one query?

Answer 1

habrahabr.ru/blogs/mysql/70479/

Answer 2

We try. All measurements on a table with 5000 records, random cid from 1 to 10, id primary, per cid index.
On PostgreSQL you can query

SELECT a.*
FROM somedata AS a
WHERE a.id IN (
  SELECT b.id
  FROM somedata AS b
  WHERE b.cid = a.cid
  ORDER BY b.id DESC
  LIMIT 5
)
ORDER BY a.cid DESC, a.id DESC

But the query is slow (0.125 sec), and the execution time grows in direct proportion to the number of records and categories. MySQL does not support LIMIT at all in nested queries, let's move on. We make a scary monster:

SELECT a.*
FROM somedata AS a
WHERE a.id IN (
  SELECT id
  FROM somedata AS b
  WHERE b.cid = a.cid AND (SELECT COUNT(*) FROM somedata AS c WHERE c.id >= b.id AND c.cid = b.cid) <= 5
)
ORDER BY a.cid DESC, a.id DESC

Of course, he coped, the correct data was received, but the execution of such a request took ... 15.87 seconds. Don't want to, right? :)
The aesthetically ugly ugly solution turned out to be the most productive. We glue queries in PHP for each category into a single one using UNION:

(SELECT * FROM somedata WHERE cid = 1 ORDER BY id DESC LIMIT 5)
UNION
(SELECT * FROM somedata WHERE cid = 2 ORDER BY id DESC LIMIT 5)
UNION
(SELECT * FROM somedata WHERE cid = 3 ORDER BY id DESC LIMIT 5)

etc. to cid = 10, and lo and behold: the query is executed on MySQL in 0.002 seconds, giving the desired result.
Although, perhaps I missed some obvious solution with acceptable performance. If you find it, tell us :)

Answer 3

order by `id` desc limit 5?

Answer 4

And what do we consider the first, what is the last? id?

Answer 5

Try:
SELECT `t1`.`id`, `t1`.`cid`, `t1`.`title`, COUNT(*) as `counter` FROM `test` `t1` JOIN `test` `t2` ON `t1`.`cid` = `t2`.`cid` AND `t1`.`id` >= `t2`.`id` GROUP BY `t1`.`cid`, `t1`.`id` HAVING `counter` <= 5 ORDER BY `t1`.`cid`, `t1`.`id`;

Answer 6

5 selects and 1 query including 5 UNION ALL selects would be the best solution for a working project.