Запрос вложенного множества SQL с объединением и подзапросом с использованием медленного "in"

Question

У меня возникли проблемы с медленным запросом к структуре вложенного множества (~4 сек.)

select node.ID, node.Lft, node.Rgt, node.Level, count(ag.ArticleID) as count
from Webshop.Category node
left join Webshop.Category parent on parent.Lft between node.Lft and node.Rgt
left join Webshop.ArticleGroup ag on parent.GroupID = ag.GroupID
    and ag.ArticleID in (
        select a.ID
        from Webshop.Article a
        join Webshop.ArticleOwner ao on ao.ArticleID = a.ID and ao.OWNRID = 1
        join Webshop.ArticleAssortment aa on aa.ArticleID = a.ID and aa.AssortmentID = 6
    )
group by node.ID
having count > 0

Объяснение возвращает следующее:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,node,ALL,NULL,NULL,NULL,NULL,2538,"Using temporary; Using filesort"
1,PRIMARY,parent,ALL,Lft,NULL,NULL,NULL,2538,
1,PRIMARY,ag,ref,fk_ArticleGroup_Group1_idx,fk_ArticleGroup_Group1_idx,4,Webshop.parent.GroupID,9,"Using index"
2,"DEPENDENT SUBQUERY",a,eq_ref,PRIMARY,PRIMARY,4,func,1,"Using index"
2,"DEPENDENT     SUBQUERY",ao,eq_ref,"ArticleIDOWNRID,fk_ArticleOwner_Article1_idx,fk_ArticleOwner_OWNR1_idx",ArticleI    DOWNRID,8,"Webshop.a.ID,const",1,"Using index"
2,"DEPENDENT SUBQUERY",aa,eq_ref,"PRIMARY,fk_ArticleAssortment_Article1_idx",PRIMARY,8,"Webshop.a.ID,const",1,"Using index"

Я думаю, что подзапрос с in() делает запрос медленным. Есть лучший способ сделать это?

Благодарю.

РЕДАКТИРОВАТЬ:

Я забыл удалить left от присоединения в подзапросе.

Answer 1

Вы могли бы попробовать JOINING:

select node.ID, node.Lft, node.Rgt, node.Level, count(ag.ArticleID) as count
from Webshop.Category node
left join Webshop.Category parent on parent.Lft between node.Lft and node.Rgt
left join Webshop.ArticleGroup ag on parent.GroupID = ag.GroupID
        join (
        select a.ID as `id`
        from Webshop.Article a
        left join Webshop.ArticleOwner ao on ao.ArticleID = a.ID and ao.OWNRID = 1
        left join Webshop.ArticleAssortment aa on aa.ArticleID = a.ID and aa.AssortmentID = 6
    ) in_ids on ag.ArticleID = in_ids.id
group by node.ID
having count > 0

Answer 2

Ваш подзапрос IN содержит предложения LEFT JOIN, которые не влияют на результат, поэтому прежде всего вы можете изменить его следующим образом:

select node.ID, node.Lft, node.Rgt, node.Level, count(ag.ArticleID) as count
from Webshop.Category node
left join Webshop.Category parent on parent.Lft between node.Lft and node.Rgt
left join Webshop.ArticleGroup ag on parent.GroupID = ag.GroupID
    and ag.ArticleID in (
        select a.ID
        from Webshop.Article a
    )
group by node.ID
having count > 0

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

select node.ID, node.Lft, node.Rgt, node.Level, count(ag.ArticleID) as count
from Webshop.Category node
left join Webshop.Category parent on parent.Lft between node.Lft and node.Rgt
left join Webshop.ArticleGroup ag on parent.GroupID = ag.GroupID
group by node.ID
having count > 0

Второй вариант, у вас нет внешнего ключа. Это не плохая идея, чтобы создать его.

ОБНОВЛЕНИЕ: я только что заметил, что у вас есть count > 0 в конце запроса, что означает, что вы можете заменить все left join пункты с inner join с тем же результатом.

Как вы заменили left joins с inner join в подзапросе единственное, что может изменить скорость здесь, это замена IN с EXISTS:

select node.ID, node.Lft, node.Rgt, node.Level, count(ag.ArticleID) as count
from Webshop.Category node
inner join Webshop.Category parent on parent.Lft between node.Lft and node.Rgt
inner join Webshop.ArticleGroup ag on parent.GroupID = ag.GroupID
WHERE EXISTS (
    SELECT * FROM
        from Webshop.Article a
        join Webshop.ArticleOwner ao on ao.ArticleID = a.ID and ao.OWNRID = 1
        join Webshop.ArticleAssortment aa on aa.ArticleID = a.ID and aa.AssortmentID = 6 
    WHERE a.ID = ag.ArticleID
    )
group by node.ID
-- you don't need HAVING clause here

Answer 3

Во-первых, у вас есть загрузка левых соединений, а левые - медленные - я предполагаю, что вы используете их, потому что они необходимы здесь (т. Е. Объединенные таблицы могут не содержать совпадений, и вы хотите вернуть каждую строку Webshop.Category. независимо от наличия соединяемых строк). Если левые соединения не нужны, я бы преобразовал их во внутренние. Если необходимы левые объединения , я бы подумал о том, как улучшить дизайн базы данных, чтобы устранить необходимость в этих левых соединениях. Это может быть неизбежно, но я был бы удивлен.

Если преобразование во внутренние объединения невозможно, или если запрос все еще медленный, вы можете сначала попытаться выполнить подзапрос в качестве вставки в оператор, создать временную таблицу, а затем использовать эту временную таблицу вместо подзапроса (возможно, как объединение, а не "в").

Если это все еще медленно, вы можете попробовать проиндексировать эту временную таблицу по ID.

(Во всем вышеизложенном я предполагаю, что таблицы разумно проиндексированы?)