Slow sort by date in Mysql

Question

MySQL

there is a slow query (for 30k posts and 30k users it takes 0.2s):

SELECT *
FROM `posts`
JOIN `users` ON ​​( `posts`.`user_id` = `users`.`id` )
GROUP BY `posts`. `id`
ORDER BY `posts`.`date` DESC LIMIT
10

there are a lot of fields, but this does not affect the essence of the question, I will describe the main one - date of type datetime, ID of type int

primer indexes on posts.id and users.id + index on posts.user_id

in this situation, the query is executed in 0.2s

if you put the index on date query it still runs in 0.2s
if sorting by posts.id (which is basically equivalent to date), the request runs in 0.001s
if we remove JOIN users, the query is executed in 0.001s

explain:

no index on posts.date, time 0.2s

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
one	SIMPLE	posts	index	user_id	PRIMARY	4	NULL	1000	Using where; using temporary; Using filesort
one	SIMPLE	users	eq_ref	PRIMARY	PRIMARY	3	db_site.posts.user_id	one	Using where

with index on posts.date, time 0.2s

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
one	SIMPLE	posts	index	user_id	date	eight	NULL	1000	Using where; Using temporary
one	SIMPLE	users	eq_ref	PRIMARY	PRIMARY	3	db_site.posts.user_id	one	Using where

without sorting by posts.date, time 0.001s

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
one	SIMPLE	posts	index	user_id	PRIMARY	4	NULL	1000	Using where
one	SIMPLE	users	eq_ref	PRIMARY	PRIMARY	3	db_site.posts.user_id	one	Using where

and now attention, the question is how to make sorting by date fast? And what am I doing wrong...

Answer 1

Remove GROUP BY `posts`.`id`

Answer 2

Try a composite index (posts.id, posts.date).

Answer 3

If I were you, I would change the structure of the posts page. As far as I understand, you need the users label to display the name of the author of the post, I would add the necessary fields to the posts label (the name of the author, maybe the rating of the post, needed in general), and would not connect the rest of the labels, but in this case it is necessary when updating others tables to watch and posts. It will work many times faster, but more attention will also need to be paid to changes.