Ошибка в JOIN-SELECT

352

12 апреля 2017, 13:23

Помогите пожалуйста найти ошибку в SQL запросе.

SELECT
  dialog.id,
  dialog.fromId,
  dialog.toId,
  dialog.statusId,
  tp.firstName                                                               AS toFirstName,
  tp.lastName                                                                AS toLastName,
  tp.photoId                                                                 AS toPhotoId,
  fp.firstName                                                               AS fromFirstName,
  fp.lastName                                                                AS fromLastName,
  fp.photoId                                                                 AS fromPhotoId,
  lm.id                                                                      AS messageId,
  lm.senderId                                                                AS messageSenderId,
  lm.receiverId                                                              AS messageReceiverId,
  lm.photoId                                                                 AS messageSenderPhoto,
  (SELECT COUNT(m.id)
   FROM message m
   WHERE m.receiverId = :bv1 AND m.readState = 0 AND m.dialogId = dialog.id) AS unreadMessageCount
FROM dialog
  LEFT JOIN (SELECT
               m.id,
               m.senderId,
               m.receiverId,
               m.text,
               p.photoId,
               m.dialogId
             FROM message m
               INNER JOIN profile p ON (p.id = m.senderId)
             ORDER BY m.id DESC
             LIMIT 1
            ) AS lm ON (lm.dialogId = dialog.id)
  INNER JOIN profile tp ON tp.id = dialog.toId
  INNER JOIN profile fp ON fp.id = dialog.fromId
WHERE ((fromId = :bv1) OR (toId = :bv1)) AND statusId = :bv3
LIMIT :bv4 OFFSET :bv5

Суть запроса: запрос выбирает из таблицы dialog записи, вместе с последним сообщением, относящимся к диалогу.

Проблема: только для первого диалога находится последнее сообщение.

Answer 1

Никогда ещё Штирлиц не был так близок к провалу...LIMIT 1

Запрос на LIMIT 1 не будет формироваться каждый раз. У вас выбирается одна запись, потому что подзапрос с использованием LIMIT выполнился один раз, выбрал одну запись из таблицы и...она подходит лишь к одному диалогу.

Основной способ @Mike уже назвал, что делаем MAX(message.id) из группы и джойним Messages к MaxId:

SELECT *
FROM
  (SELECT MAX(i1.id) maxId
   FROM dialog
   LEFT JOIN message AS i1 ON i1.dialogId = dialog.id
   GROUP BY dialog.id) tbl
JOIN message ON tbl.maxId = message.id

Работает быстро и использует только индексы.

Кстати, можно написать что-то более извращенное без вынесения этого в подзапрос. Делаем JOIN таблицы message 2 раза к таблице dialog, и выбираем записи с наибольшим ID:

    SELECT i1.*
FROM dialog
LEFT JOIN message AS i1 ON i1.dialogId = dialog.id
LEFT JOIN message AS i2 ON (i1.dialogId = i2.dialogId
                            AND i1.id < i2.id)
WHERE i2.id IS NULL;

Сурово! За-то без подзапроса :)

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

Декомпозиция

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

Как вариант, можно использовать кеш для получения последних сообщений по DialogId и не использовать группировку в основном запросе совсем, а обновлять кеш по записи нового сообщения. Просто передавать dialogId в какой-нить CacheMessageProvider и получать из кеша последнее сообщение, а диалогов которых нет - принудительно грузить из базы данных отдельным запросом по ID, сокращая объем работы для базы данных.

Хотя предварительно стоит оценить страдает ли производительность для применения подобных решений?

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