Warning: Cannot use a scalar value as an array in /home/admin/public_html/forum/include/fm.class.php on line 757
Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/include/fm.class.php on line 770
Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/topic.php on line 737 Форумы портала PHP.SU :: Оптимизация запроса SQL
LEFTJOIN nutrition_program_delivery oc ON oc.order_id = d.order_id
LEFTJOIN uc_order_line_items li ON li.order_id = d.order_id AND li.type ='coupon'
LEFTJOIN uc_order_line_items li2 ON li2.order_id = d.order_id AND li2.type ='shipping'
LEFTJOIN uc_order_line_items li3 ON li3.order_id = d.order_id AND li3.type ='donate'
LEFTJOIN courier_delivery_offer cof ON cof.delivery_id = d.id AND cof.STATUS='accepted'
LEFTJOIN profile_values pv ON pv.uid = cof.courier_id AND pv.fid IN(SELECT fid FROM profile_fields WHERE name ='profile_last_name')
вот выдача explain (Добавление)
интересует как избавится от лишних 287 проходов Прикреплено изображение (Нажмите для увеличения)
Stierus
Отправлено: 11 Июня, 2013 - 09:43:51
Рекордсмен по количеству сообщений за 7 дней
Покинул форум
Сообщений всего: 2132
Дата рег-ции: Дек. 2008 Откуда: Москваль
Помог: 52 раз(а)
8 лефт джойнов и подзапрос ... неплохо )))
а по теме - если доступ к этому хозяйству дашь - посмотрю на днях
EuGen
Отправлено: 11 Июня, 2013 - 10:42:17
Профессионал
Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007 Откуда: Berlin
Помог: 707 раз(а)
biperch пишет:
LEFT JOIN uc_order_line_items li ON li.order_id = d.order_id AND li.type = 'coupon'
LEFT JOIN uc_order_line_items li2 ON li2.order_id = d.order_id AND li2.type = 'shipping'
LEFT JOIN uc_order_line_items li3 ON li3.order_id = d.order_id AND li3.type = 'donate'
Уже напрашивается таблица-связка (три JOIN самой на себя ради разных значений? а если что_либо_там_li4 появится? новое поле?).
Подзапрос - если он постоянен на странице (судя по всему) - то можно заранее выбрать в приложении и подставлять готовые значения в IN. Имеем смысл, если запрос выше используется многократно.
Выдачу explain посмотреть не могу, но ещё посоветую, конечно, убедиться, что существуют индексы (по нескольким колонкам, если требуется) для всех JOIN в запросе. (Полей условия отбора не наблюдаю, но если они есть - индексы потребуются и там)
----- Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
Champion
Отправлено: 11 Июня, 2013 - 11:59:58
Активный участник
Покинул форум
Сообщений всего: 4350
Дата рег-ции: Авг. 2008 Откуда: Москва
Что я сделал. Заменил три лефт-джойна на кросс-джойн с кейсом. Это иногда бывает быстрее лефт джойнов. Посмотрите, действительно ли будет прирост в скорости у вас и если да, то пользуйтесь.
У вас в запросе используется COUNT(d.id). Запрос скорее всего делает не то,что вы хотели, а возвращает одну строчку (потому что не указан GROUP BY). Если я прав, то для этого в моем варианте в подзапросе один раз вычисляется count и потом используется вычисленный cntAll.
Убрал LEFT JOIN nutrition_program_delivery oc ON oc.order_id = d.order_id, потому что он нигде не используется.
Переписал IN в JOIN. Mysql(по крайней мере 5.5) отвратительно оптимизирует IN, как правило, преобразовывая его в exists и вычисляя для каждой строки. Maria, например, так не делает.
Что еще можно сделать:
Убедиться, что по полям, по которым фильтруются данные есть индексы и по полям, по которым джойнятся таблицы - тоже есть индексы.
Переписать лефт-джойн в иннер-джойн, если он подходит по смыслу, такой запрос будет качественнее оптимизироваться самой СУБД.
biperch
Отправлено: 11 Июня, 2013 - 15:23:12
Частый посетитель
Покинул форум
Сообщений всего: 588
Дата рег-ции: Окт. 2009 Откуда: Днепропетровск
Помог: 8 раз(а)
Спасибо интересные моменты, да у меня есть GROUP BY d.id что позволяло правильно работать COUNT(d.id) суть тут в том что у каждой записи в nutrition_program_delivery могут быть подобные то есть из этого же заказа и для каждой доставки нужно высчитать стоимость ее имея общую стоимость заказа то есть делю стоимость заказа на количество доставок
o.order_total/COUNT(d.id) AS price
за счет GROUP BY, COUNT(d.id) давал количество доставок для каждого заказа
А в вашем примере Champion, cntAll это количество всех записей но это не ваша вина
С крос джойнами не работал и с Юнионами, не приходилось
Пример очень интересный, обещаю покурить CROSS JOIN и SELECT 'coupon' liType UNION ALL SELECT 'shipping' UNION ALL 'donate' но последнее как то укладывается (Добавление)
EuGen пишет:
biperch пишет:
LEFT JOIN uc_order_line_items li ON li.order_id = d.order_id AND li.type = 'coupon'
LEFT JOIN uc_order_line_items li2 ON li2.order_id = d.order_id AND li2.type = 'shipping'
LEFT JOIN uc_order_line_items li3 ON li3.order_id = d.order_id AND li3.type = 'donate'
Уже напрашивается таблица-связка (три JOIN самой на себя ради разных значений? а если что_либо_там_li4 появится? новое поле?).
Подзапрос - если он постоянен на странице (судя по всему) - то можно заранее выбрать в приложении и подставлять готовые значения в IN. Имеем смысл, если запрос выше используется многократно.
Выдачу explain посмотреть не могу, но ещё посоветую, конечно, убедиться, что существуют индексы (по нескольким колонкам, если требуется) для всех JOIN в запросе. (Полей условия отбора не наблюдаю, но если они есть - индексы потребуются и там)
Спасибо, все ключевые поля которые участвуют в условиях про индексированы
вложенный запрос судя по explain представляется в виде константы (Добавление)
Этот запрос не на постоянной основе работает, а это часть из выборки статистики по заказам, будет запускаться ну пару раз в день
Champion
Отправлено: 11 Июня, 2013 - 21:55:18
Активный участник
Покинул форум
Сообщений всего: 4350
Дата рег-ции: Авг. 2008 Откуда: Москва
Помог: 57 раз(а)
biperch пишет:
База данных ругается на
Точно, select пропущен в последнем unione.
biperch
Отправлено: 11 Июня, 2013 - 23:43:48
Частый посетитель
Покинул форум
Сообщений всего: 588
Дата рег-ции: Окт. 2009 Откуда: Днепропетровск
Помог: 8 раз(а)
Champion пишет:
biperch пишет:
База данных ругается на
Точно, select пропущен в последнем unione.
да и еще вы пропустили один join LEFT JOIN profile_values pv ON pv.uid = cof.courier_id
biperch
Отправлено: 11 Июня, 2013 - 23:45:25
Частый посетитель
Покинул форум
Сообщений всего: 588
Дата рег-ции: Окт. 2009 Откуда: Днепропетровск
Помог: 8 раз(а)
вот explain Прикреплено изображение (Нажмите для увеличения)
Champion
Отправлено: 12 Июня, 2013 - 12:31:12
Активный участник
Покинул форум
Сообщений всего: 4350
Дата рег-ции: Авг. 2008 Откуда: Москва
Помог: 57 раз(а)
biperch пишет:
еще вы пропустили один join LEFT JOIN profile_values pv
SELECTDISTINCT fid FROM profile_fields WHERE name ='profile_last_name'
) t
LEFTJOIN profile_values pv ON pv.uid = cof.courier_id AND pv.fid = t.fid
И создать для этого один индекс по pv.uid и pv.fid вместе.
Еще, как говорили, подзапрос к profile_fields можно выполнить заранее и подставить полученные значения. Но это нехорошо, если он может возвращать большое количество записей.
Еще у меня все-таки есть предположение, что большинство лефл джойнов могут быть иннер. Это должно позволить уменьшить число 4636 из експлейна.
А вообще, если вы приложите дампчик, я с интересом поэксперементирую.
Все гости форума могут просматривать этот раздел. Только зарегистрированные пользователи могут создавать новые темы в этом разделе. Только зарегистрированные пользователи могут отвечать на сообщения в этом разделе.