Подзапрос в условии ON для LEFT JOIN'а в MySQL

Я перекопал весь стековерфлов и утомил гугл, но я так и не понял почему MySQL запрос (ниже) работает не правильно. Прошу протянуть руку помощи или швырнуть в лицо доку).
Задача:
Нужно получить самую свежую дату из таблицы статистики для каждого юзера.
ER-диаграмма (упрощенная):
image
SQL запрос:
SELECT U.*, S.*
FROM `user` AS U
LEFT JOIN stats AS S ON S.user_id = (
  SELECT user_id
  FROM stats AS S2
  # Без этого условия джойнит одну запись, что и нужно. Но, соответственно, ID юзера не соответствует
  WHERE S2.user_id = U.id
  ORDER BY S2.date DESC
  LIMIT 1
)
WHERE U.id = 1


Проблема:
Почему не работает LIMIT при условии WHERE в подзапросе?
Можно получить дату простым подзапросом, но мне нужно получить дополнительные поля, которые я не указывал в упрощенной схеме. И таких полей ~ 5, т.е. будет 5 подзапросов. Что будет быстрее не уверен, но пока склоняюсь что LEFT JOIN будет быстрее. Да и вопрос уже принципиален — пол дня убил.

Дамп БД на gist'e (30 строк) и тот же дамп для скачивания.

Заранее спасибо за помощь.
  • Вопрос задан
  • 23099 просмотров
Решения вопроса 1
Sorbing
@Sorbing Автор вопроса
В итоге, я добавил суррогатный PK, вместо 3-х составных полей. Если не учитывать денормализацию БД, считаю это оптимальным решением. Итоговый запрос:
SELECT U.*, S.*
FROM `user` AS U
LEFT JOIN stats AS S 
  ON S.id = (
    SELECT id
    FROM stats AS S2 
    WHERE S2.user_id = U.id
    ORDER BY id DESC
    LIMIT 1
  )

Я получаю необходиммые мне данные — все поля последней записи таблицы stats с любым типом (можно указать конкретный тип) для каждого юзера. При этом сортировка по ID будет быстрее, чем по дате. Запись для JOIN'а теперь однозначная, ситуация выборкой нескольких записей статсы с одинаковой датой решена.
Осталось прогнать на более-менее реальном наборе данных и оценить производительность.
Всем спасибо за помощь.
Ответ написан
Пригласить эксперта
Ответы на вопрос 8
FreeTibet
@FreeTibet
dharma supplier
Понимаю, что этот не тот ответ, которого вы ждёте, но если не ошибаюсь, вот такой запрос будет работать правильно:
SELECT U.*, S.*
FROM `user` AS U
LEFT JOIN stats AS S 
  ON S.user_id = u.ID 
AND s.date = (
SELECT max(date) 
  FROM stats AS S2 
  WHERE s2.user_id = u.id
)


WHERE U.id =1 — по желанию :)
Ответ написан
sHinE
@sHinE
веб-разработчик, php/js/mysql и сопутствующее
Вот похожий вопрос был — habrahabr.ru/qa/437/
Ответ написан
SLY_G
@SLY_G
журналист, переводчик, программист, стартапщик
Простите, может я не очень понял.
Но по-моему, можно как-то проще:

SELECT user.ud, MAX(stats.date) FROM user LEFT JOIN stats ON stats.user_id = user.id 


не проверял, честно говорю.
Ответ написан
@egorinsk
Я бы лучше сделал SELECT user_id, MAX(date) FROM stats GROUP BY user_id, а потом бы приджойнил юзеров запросом из приложения вроде SELECT * FROM users WHERE user_id IN (...). Что-то я сомневаюсь, что четырехэтажные запросы с подзапросами будут быстро работать, да еще и в MySQL.
Ответ написан
Shedal
@Shedal
Не работает ваш запрос потому, что вы из подзапроса обратно выбираете тот же самый user_id, который «подали вовнутрь». Иными словами, ваш запрос идентичен следующему:

SELECT U.*, S.*
FROM `user` AS U
LEFT JOIN stats AS S ON S.user_id = U.id
WHERE U.id = 1

Как правильно сделать, уже написали выше. ИМХО, вариант с группировкой будет лучше всего, и читабельнее тоже. Хотя лучше сравните производительность на реальных данных. На всякий случай, держите сам запрос:

SELECT u.*, MAX(s.date) max_date
FROM user u
LEFT JOIN stats s
  ON s.user_id = u.id
WHERE u.id = 1
GROUP BY u.id
Ответ написан
@MikhailEdoshin
А если LIMIT убрат, он со всеми записями соединит? В SQLite, например, можно использовать внутренний идентификатор ряда без лимита, т. е. это было бы:

SELECT u.*, s.*
  FROM user u
  JOIN stat s ON s.rowid = (
       SELECT t.rowid
         FROM stat t
        WHERE t.user_id = u.id
     ORDER BY t.date DESC)
 WHERE u.id = ?

но там явно оговаривается, что (SELECT y ...) в выражении возвращает первую совпавшую запись, а не все; не знаю, как с этим в MySQL.
Ответ написан
@bugman
Если таблица stats представляет собой slowly changing dimensions и перформанс запроса на получения актуальной статистики важен, я бы подумал о других подходах как например денормализовать stats и хранить актуальные значения статистики в отдельной таблице (или даже у юзера), stats же пользовать как аудит изменений.
Ответ написан
@bugman
Если не нравится паттерн с созданием аттрибутов статистики в сущности клиента, попробуйте тогда, в самом простом случае, держать одну таблицу для актуальных значений статистики с PK [user_id, stat_type] + таблицу с историческими значениями (аудит таблицу), которую будете сопровождать на триггерах, в случае изменении первой.

В еще более хитром случае, эти таблицы можно и объединить (как у вас сейчас — изначально), но для ускорения запросов добавить флаг current, который будет либо 1 либо NULL + составной индекс [current, user_id].
Геммор триггеров из предыдущего варианта уйдет, заменит его геммор сопровождения флага current.
Всевозможные варианты организации подобных исторических справочников с "+" и "-" описаны в википедийной статье на тему «slowly changing dimensions».

Задайте себе вопрос, кроме багтрейсов и редкой аналитики, Вам действительно принципиально гонять запросы на получение и исторических и актуальных данных по одной таблице? Если нет — первый вариант самое то + партицирование и удаление самых старых партиций на шедулере (по вкусу).
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы