How to choose a single query last 5 records of each category in MySQL?

Try. All measurements on the table with 5000 entries, random cid from 1 to 10, primary id, cid index.
\r
In PostgreSQL you can query
\r

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 increases in direct proportion to the count of records and categories. MySQL does not support LIMIT in subqueries, go ahead. Master terrible monster:
\r

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

It is, of course, right, received the correct data, but the execution of such a request... took 15.87 seconds. Not want, right? :)
\r
The most productive turned out to be aesthetically ugly ugly. The bonding in PHP, the queries for each category in a single by using a UNION:
\r

(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 runs on MySQL for 0.002 seconds, giving the desired result.
\r
Although, maybe I missed some obvious solution with acceptable performance. If you find – tell us :)

order by `id` desc limit 5?

What we consider first, what last? id?

Try:
\r
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`;

5 select points and 1 request that includes the 5 selects with UNION ALL is the best solution for a work project.