Форум Сайтостроение Веб-строительство

LEFT JOIN медленный если есть ORDER BY

12 3
lutskboy
На сайте с 22.11.2013
Offline
170
lutskboy
1420

Привет

есть две таблици p и e

в таблице е есть поле rating на котором index. записей около 500 тыс

запрос вида 


SELECT p.id, p.autor, p.date,  p.title, p.category,  e.rating, e.vote_num FROM dle_post p 

LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE p.category IN ('1') AND date < '2019-09-14 16:22:23' AND approve=1   LIMIT 0,100

работает быстро. но если мы добавляем ORDER BY rating то беда. по 15 сек запрос. при чем прикол в том что если ставить

ORDER BY date или ORDER BY fixed. где поля date и fixed от таблици р то тоже быстро. но если брать любое поле с таблици е и сделать по нему ORDER BY то 15-17сек

IL
На сайте с 20.04.2007
Offline
435
ivan-lev
#1

Добавьте индекс в таблицу e 

 (news_id, rating)
... :) Облачные серверы от RegRu - промокод 3F85-3D10-806D-7224 ( http://levik.info/regru )
lutskboy
На сайте с 22.11.2013
Offline
170
lutskboy
#2

не помогло.

IL
На сайте с 20.04.2007
Offline
435
ivan-lev
#3
lutskboy:
не помогло.

Что Explain говорит?

lutskboy
На сайте с 22.11.2013
Offline
170
lutskboy
#4

я в нем не разбираюсь. но вот ето http://skrinshoter.ru/s/160919/6i1R64AI

IL
На сайте с 20.04.2007
Offline
435
ivan-lev
#5

Есть предположения.. пробовать надо.

Если нет опасений - можете скинуть доступ в личку. Кроме добавления индексов и запросов на выборку делать ничего не буду..

LEOnidUKG
На сайте с 25.11.2006
Offline
1722
LEOnidUKG
#6

Я бы ещё профилирование сделал запроса, чтобы точно посмотреть, из-за чего там идёт такое.

Ещё вариант, вместо JOIN заюзать подзапрос SELECT, и потом просто связать их.

✅ Мой Телеграм канал по SEO, оптимизации сайтов и серверов: https://t.me/leonidukgLIVE ✅ Качественное и рабочее размещение SEO статей СНГ и Бурж: https://getmanylinks.ru/
IL
На сайте с 20.04.2007
Offline
435
ivan-lev
#7
lutskboy:
но если брать любое поле с таблици е и сделать по нему ORDER BY то 15-17сек

Да, фактически так и получается https://forums.mysql.com/read.php?115,519488,520284#msg-520284

only way to improve the query performance is to have the filtering and sorting columns in the same table and create a combined index for them?

Единственный способ - иметь поля, по которым проводится отбор и сортировка в одной таблице.

Yes. This is because it cannot do the LIMIT until after the SORT (ORDER BY), which cannot start until it has looked at all the rows in both tables.
One possibility -- Maintain (redundantly) another table with _only_ the valid rows.

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

Т.е. по фэн-шую продублировать rating в первую таблицу.. и обновлять его.. или триггером, или по cron-у.

---------- Добавлено 16.09.2019 в 20:50 ----------

LEOnidUKG:
Я бы ещё профилирование сделал запроса, чтобы точно посмотреть, из-за чего там идёт такое.

Из-за temporary + filesort и идёт.

LEOnidUKG
На сайте с 25.11.2006
Offline
1722
LEOnidUKG
#8
Единственный способ - иметь поля, по которым проводится отбор и сортировка в одной таблице.

У меня дикая идея, если по рейтингу сортировать то менять местами JOIN :)

IL
На сайте с 20.04.2007
Offline
435
ivan-lev
#9
LEOnidUKG:
У меня дикая идея, если по рейтингу сортировать то менять местами JOIN

https://www.percona.com/blog/2006/09/01/mysql-order-by-limit-performance-optimization/

Sort by column in leading table if you have JOIN with ORDER BY … LIMIT you should try hard to have sorting column(s) to be in the leading table. If ORDER BY is going by field from the table which is not first in the join order index can’t be used. Sometimes it means breaking normalization and duplicating column(s) you’re going to use in ORDER BY in other table.

Но, судя по Explain-у, MySQL (поумнел)) вполне нормально пользует индексы из "вторых" таблиц (если не используется сортировка по полям первой, конечно))

Конкретно в этом случае есть более интересный вариант.. :D

SELECT p.id, p.autor, p.date, p.title, p.category, e.rating, e.vote_num FROM dle_post p FORCE INDEX(PRIMARY)
LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE p.category IN ('1') AND date < '2019-09-14 16:22:23' AND approve=1 LIMIT 0,100
CMS для потрала с Сайт и нейросети, что Почему MySQL server has
LEOnidUKG
На сайте с 25.11.2006
Offline
1722
LEOnidUKG
#10
FORCE INDEX(PRIMARY)

И прям спасает ситуацию?

12 3

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий