Медленная сортировка по дате в Mysql

Question

[DexizeR]

имеется медленный запрос (для 30к постов и 30к юзеров выполняется за 0.2с):

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

описывать стуктуру таблиц не буду полностью, т.к. полей много а на суть вопроса это не влияет, опишу основное — date типа datetime, ID типа int

праймери индексы на posts.id и users.id + индекс на posts.user_id

при таком раскладе запрос выполняется за 0.2с

если поставить индекс на date запрос выполняется все равно за 0.2с
если поставить сортировку по posts.id (что в принципе эквивалентно date), запрос выполняется за 0.001с
если убрать JOIN users запрос выполняется за 0.001с

explain:

без индекса на posts.date, время 0.2с

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

с индексом на posts.date, время 0.2с

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

без сортировки по posts.date, время 0.001с

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

а теперь внимание, вопрос — как сделать сортировку по дате быстрой? и что я делаю не так…

Answer 1

[rvsob]

Уберите GROUP BY `posts`.`id`

Comments

[DexizeR]

убрал, стало выполняться за 0.025, спасибо! а почему так?

[homm]

Вообще не понятно, что там делает группировка.

[DexizeR]

поторопился, даж без группировки иногда до 0.2с доходит, хотя и побыстрее конечно исполняется

[DexizeR]

а вот если добавить индекс на posts.date то исполняется за 0.007, еще раз спасибо, за сим закрываю вопрос

[homm]

Скорость нужно тестировать с директивой SQL_NO_CACHE после SELECT.

[DexizeR]

все результаты показаны с этой директовой

Answer 2

[JeanLouis]

Попробуйте составной индекс (posts.id, posts.date).

Comments

[JeanLouis]

И попробуйте вариант наоборот, если вдруг сортировка выполняется перед группировкой, я сейчас не помню этот момент уже.

[DexizeR]

пробовал такой составной индекс, результат не изменился…

Answer 3

[Анатолий]

Я бы на Вашем месте изменил бы структуру страницы posts. На сколько я понимаю табличка users Вам нужна для отображения имени автора поста, я бы добавил в табличку posts нужные поля ( имя автора, может рейтинг поста, нужные в общем), и не соединял бы остальные таблички, но в таким случае нужно при обновлении других таблиц смотреть и посты. Работать будет в разы быстрей, но внимания нужно будет тоже больше уделить изменениям.

Comments

[DexizeR]

да, такое решение мне известно, я неподдающиеся оптимизации вещи храню в быстой key value базе… но хотелось разобраться в проблеме, а не искать сразу обходных путей… спасибо

[Анатолий]

В том то и дело, что это реальное решение проблемы, которое отлично увеличивает производительность. Вторая табличка Вам не нужна при показе блогов, но Вы ее упорно подключаете и хотите увеличить при этом скорость, это не рационально подключать табличку из-за одного поля.