Почему MySQL не верно сортирует при условии + группировке?

Question

[Deprimovec]

Доброго времени суток.
Есть запрос, к примеру:

SELECT id, thread_id, device_id, body, date FROM message WHERE device_id = 12 GROUP BY thread_id ORDER BY date LIMIT 0, 50

thread_id - идентификатор беседы, с той или иной персоной. device_id - идентификатор устройства. При этом на данный момент в этой табличке присутствует только этот device_id = 12.

Этот запрос должен вывести список сообщений для указанного device_id, сгруппировать их по беседе и отсортировать их по дате.
Если убрать условие WHERE device_id = 12, то группировка и сортировка проходят правильно.
Но если присутствует условие, то на выходе сортировка по дате не корректная.

По каким причинам условие WHERE device_id = N может вредить сортировке?

Comments

[IceJOKER]

а у даты какой тип? если date/datetime попробуйте UNIX_TIMESTAMP(date)

[IceJOKER]

...в сортировке

[Deprimovec]

IceJOKER: datetime все верно

[Deprimovec]

IceJOKER: Попробовал - все абсолютно также

Answer 1

[Deprimovec]

Публикую нагугленный вариант ответа.
Скорость запроса конечно с 0.042 упала до 0.115 (все пока что без индексов), но все же - это выход.

SELECT * FROM (SELECT * FROM message WHERE device_id=12 ORDER BY date DESC) AS msg WHERE device_id=12 GROUP BY thread_id ORDER BY date DESC LIMIT 0 , 50

Answer 2

[Dimonchik]

рецепт отсюда не помог?

дело не в WHERE device_id = N а в одновременном GROUP и ORDER BY, это частый случай

Comments

[Deprimovec]

Все верно. Дело не в условии -_- И, к сожалению, рецепт не помог.

Answer 3

[Станислав Макаров]

Я может чего путаю - но вам тут GROUP BY вообще зачем? Где агрегирующее выражение? Что значит "сгруппировать по беседе"? Может вам нужна сортировка одновременно по дате и беседе?

Необходимо вывести список бесед (а вернее последнее сообщение из каждой беседы). Беседа обозначена через thread_id. По этой самой причине идет группировка по беседе и сортировка по дате.

Теперь понятно, попробуйте так:

select thread_id, body, date from
		(select message.thread_id, max(message.date) as date from message where device_id = 12 group by thread_id) as last_message
	join
		message
	on message.thread_id = last_message.thread_id and message.date = last_message.date
limit 0, 50

Comments

[Deprimovec]

Необходимо вывести список бесед (а вернее последнее сообщение из каждой беседы). Беседа обозначена через thread_id. По этой самой причине идет группировка по беседе и сортировка по дате.

[Станислав Макаров]

Андрей Дюгаев обновил ответ