запрос с зависимыми подзапросами слишком медленный

Question

запрос с зависимыми подзапросами слишком медленный

select  mt.from_user, mt.to_user, mt.group_id, g.name, g.created_by as adminuser,
msg.*,
(
SELECT  id
from  messages
where  t.thread_id = thread_id
and  id NOT IN (
SELECT  message_id  from  message_deleted
where  user_id=275  and  status='deleted' )
order by  CreatedDate DESC
limit  1
) as msgid,
(
SELECT  CreatedDate
from  messages
where  t.thread_id = thread_id
and  id NOT IN (
SELECT  message_id  from  message_deleted
where  user_id=275  and  status='deleted'  )
order by  CreatedDate DESC
limit  1
) as msgDate
from  user_thread as t
left join  message_thread as mt  ON t.thread_id = mt.id
left join  group_master as g  ON mt.group_id = g.id
left join  group_member as gm  ON gm.group_id = g.id
left join  messages as msg  ON t.thread_id = msg.thread_id
where  (      gm.user_id=275
or  msg.from_id=275
or  msg.to_id=275
)
and  t.status = 'Active'
group by  mt.id
order by  msgDate DESC

Это займет около 50 сек.

В приведенном выше коде я попытался разделить вышеуказанный запрос и отметить, что подзапрос занимает слишком много времени для выполнения. Могу ли я преобразовать подзапрос в соединение? Помогите мне, пожалуйста. Я застрял. Обратите внимание, что все таблицы, которые объединяются, необходимы.

    (
SELECT  id
from  messages
where  t.thread_id = thread_id
and  id NOT IN (
SELECT  message_id  from  message_deleted
where  user_id=275  and  status='deleted' )
order by  CreatedDate DESC
limit  1
) as msgid,
(
SELECT  CreatedDate
from  messages
where  t.thread_id = thread_id
and  id NOT IN (
SELECT  message_id  from  message_deleted
where  user_id=275  and  status='deleted'  )
order by  CreatedDate DESC
limit  1
) as msgDate

1

codeigniter mysql php query-performance

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Во-первых, вы неправильно используете пресловутое расширение MySQL для GROUP BY, Это, вероятно, приведет к тому, что ваши результаты будут непредсказуемыми. Прочитай это. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Во-вторых, у вас есть пара вложенных зависимых подзапросов. Первый из них это.

(select id
from messages
where t.thread_id = thread_id
and id NOT IN (select message_id
from message_deleted
where user_id=275
and status='deleted')
order by CreatedDate DESC limit 1) as msgid

Такие вложенные зависимые подзапросы, как известно, плохо работают. Они даже хуже, когда они содержат LIMIT статьи. Ваш путь к исправлению этого — рефакторинг в независимый запрос, а затем присоединение к нему.

Это может заменить запрос, чтобы найти самое последнее неосуществленное сообщение в цепочке.

          SELECT MAX(m.id) id, m.thread_id
FROM messages m
LEFT JOIN message_deleted d
ON m.id = d.id
AND d.user_id = 275
AND d.status = 'deleted'
WHERE d.id IS NULL
GROUP BY m.thread_id

Это использует LEFT JOIN .... IS NULL картина вместо NOT IN, Это быстрее Он использует MAX(id) метод поиска самой последней строки в таблице вместо ORDER BY CreatedDate DESC LIMIT 1 метод, который также намного быстрее. Это хорошо, потому что гарантированно генерирует либо 0, либо 1 строку на значение thread_id, Это означает, что вы можете использовать его в LEFT JOIN ... ON ... thread_id операция и не добавлять какие-либо строки в ваш набор результатов.

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

SELECT whatever,
q.id, r.CreatedDate
FROM whatever
LEFT JOIN (
SELECT MAX(m.id) id, m.thread_id
FROM messages m
LEFT JOIN message_deleted d
ON m.id = d.id
AND d.user_id = 275
AND d.status = 'deleted'
WHERE d.id IS NULL
GROUP BY m.thread_id
) q ON q.id = t.id
LEFT JOIN messages r ON r.id = q.id

Второй LEFT JOIN операция здесь используется для получения CreatedDate значение самого последнего сообщения, не восстановленного в таблице сообщений.

0