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

 PHP.SU

 Программирование на PHP, MySQL и другие веб-технологии
PHP.SU Портал     На главную страницу форума Главная     Помощь Помощь     Поиск Поиск     Поиск Яндекс Поиск Яндекс     Вакансии  Пользователи Пользователи


 Страниц (1): [1]   

> Без описания
Поиск в теме | Версия для печати
biperch
Отправлено: 11 Июня, 2013 - 00:43:46
Post Id



Частый посетитель


Покинул форум
Сообщений всего: 588
Дата рег-ции: Окт. 2009  
Откуда: Днепропетровск


Помог: 8 раз(а)
Добрый день
Подскажите как оптимизировать запрос

CODE (SQL):
скопировать код в буфер обмена
  1.  
  2. SELECT  d.id,
  3.                             d.order_id,
  4.                             o.order_total/COUNT(d.id) AS price,
  5.                             IFNULL(li.amount/COUNT(d.id), 0) AS coupon,
  6.                             IFNULL(li2.amount/COUNT(d.id), 0) AS shipping,
  7.                             IFNULL(li3.amount/COUNT(d.id), 0) AS donate,
  8.                             o.delivery_last_name,
  9.                             o.primary_email,
  10.                             FROM_UNIXTIME(d.delivery_time, '%Y-%m-%d %H:%i') AS delivery_time,
  11.                             d.delivery_timeframe,
  12.                             FROM_UNIXTIME(d.end_delivery_time, '%Y-%m-%d %H:%i') AS end_delivery_time,
  13.                             d.delivered,
  14.                             pv.value
  15.                     FROM nutrition_program_delivery d
  16.                     LEFT JOIN uc_orders o ON o.order_id = d.order_id
  17.                     LEFT JOIN nutrition_program_delivery oc ON oc.order_id = d.order_id
  18.                     LEFT JOIN uc_order_line_items li ON li.order_id = d.order_id AND li.type = 'coupon'
  19.                     LEFT JOIN uc_order_line_items li2 ON li2.order_id = d.order_id AND li2.type = 'shipping'
  20.                     LEFT JOIN uc_order_line_items li3 ON li3.order_id = d.order_id AND li3.type = 'donate'
  21.                     LEFT JOIN courier_delivery_offer cof ON cof.delivery_id = d.id AND cof.STATUS = 'accepted'
  22.                     LEFT JOIN profile_values pv ON pv.uid = cof.courier_id   AND pv.fid IN (SELECT fid FROM profile_fields WHERE name = 'profile_last_name')
  23.  


вот выдача explain
(Добавление)
интересует как избавится от лишних 287 проходов
Прикреплено изображение (Нажмите для увеличения)
Снимок экрана - 11.06.2013 - 00:43:07.png
 
 Top
Stierus Супермодератор
Отправлено: 11 Июня, 2013 - 09:43:51
Post Id



Рекордсмен по количеству сообщений за 7 дней


Покинул форум
Сообщений всего: 2132
Дата рег-ции: Дек. 2008  
Откуда: Москваль


Помог: 52 раз(а)
8 лефт джойнов и подзапрос ... неплохо Улыбка)))

а по теме - если доступ к этому хозяйству дашь - посмотрю на днях
 
My status
 Top
EuGen Администратор
Отправлено: 11 Июня, 2013 - 10:42:17
Post Id


Профессионал


Покинул форум
Сообщений всего: 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 в запросе. (Полей условия отбора не наблюдаю, но если они есть - индексы потребуются и там)


-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
 
 Top
Champion Супермодератор
Отправлено: 11 Июня, 2013 - 11:59:58
Post Id



Активный участник


Покинул форум
Сообщений всего: 4350
Дата рег-ции: Авг. 2008  
Откуда: Москва


Помог: 57 раз(а)
Можно попробовать так:
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT DISTINCT  
  2.                 d.id,
  3.                 d.order_id,
  4.                 o.order_total/cntAll AS price,
  5.                 coalesce(CASE WHEN liType = 'coupon' AND li.type = liType THEN li.amount/cntAll END, 0) coupon,
  6.                 coalesce(CASE WHEN liType = 'shipping' AND li.type = liType THEN li.amount/cntAll END, 0) shipping,
  7.                 coalesce(CASE WHEN liType = 'donate' AND li.type = liType THEN li.amount/cntAll END, 0) donate,
  8.                 o.delivery_last_name,
  9.                 o.primary_email,
  10.                 FROM_UNIXTIME(d.delivery_time, '%Y-%m-%d %H:%i') AS delivery_time,
  11.                 d.delivery_timeframe,
  12.                 FROM_UNIXTIME(d.end_delivery_time, '%Y-%m-%d %H:%i') AS end_delivery_time,
  13.                 d.delivered,
  14.                 pv.value
  15. FROM nutrition_program_delivery d
  16. CROSS JOIN (
  17.         SELECT count(*) cntAll FROM nutrition_program_delivery
  18. ) tcnt
  19. LEFT JOIN uc_orders o ON o.order_id = d.order_id
  20. LEFT JOIN uc_order_line_items li ON li.order_id = d.order_id AND li.type IN ('coupon', 'shipping', 'donate')
  21. LEFT JOIN courier_delivery_offer cof ON cof.delivery_id = d.id AND cof.STATUS = 'accepted'
  22. LEFT JOIN (
  23.         SELECT DISTINCT fid FROM profile_fields WHERE name = 'profile_last_name'
  24. ) t ON pv.fid = t.fid
  25. CROSS JOIN (
  26.         SELECT 'coupon' liType UNION ALL SELECT 'shipping' UNION ALL 'donate'
  27. ) t2
  28.  

Что я сделал. Заменил три лефт-джойна на кросс-джойн с кейсом. Это иногда бывает быстрее лефт джойнов. Посмотрите, действительно ли будет прирост в скорости у вас и если да, то пользуйтесь.
У вас в запросе используется 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, например, так не делает.

Что еще можно сделать:
Убедиться, что по полям, по которым фильтруются данные есть индексы и по полям, по которым джойнятся таблицы - тоже есть индексы.
Переписать лефт-джойн в иннер-джойн, если он подходит по смыслу, такой запрос будет качественнее оптимизироваться самой СУБД.
 
 Top
biperch
Отправлено: 11 Июня, 2013 - 15:23:12
Post Id



Частый посетитель


Покинул форум
Сообщений всего: 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 это количество всех записей но это не ваша вина

База данных ругается на
CODE (SQL):
скопировать код в буфер обмена
  1. CROSS JOIN (
  2.         SELECT 'coupon' liType UNION ALL SELECT 'shipping' UNION ALL 'donate'
  3. ) t2


С крос джойнами не работал и с Юнионами, не приходилось
Пример очень интересный, обещаю покурить 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 представляется в виде константы
(Добавление)
Этот запрос не на постоянной основе работает, а это часть из выборки статистики по заказам, будет запускаться ну пару раз в день
 
 Top
Champion Супермодератор
Отправлено: 11 Июня, 2013 - 21:55:18
Post Id



Активный участник


Покинул форум
Сообщений всего: 4350
Дата рег-ции: Авг. 2008  
Откуда: Москва


Помог: 57 раз(а)
biperch пишет:
База данных ругается на
Точно, select пропущен в последнем unione.
 
 Top
biperch
Отправлено: 11 Июня, 2013 - 23:43:48
Post Id



Частый посетитель


Покинул форум
Сообщений всего: 588
Дата рег-ции: Окт. 2009  
Откуда: Днепропетровск


Помог: 8 раз(а)
Champion пишет:
biperch пишет:
База данных ругается на
Точно, select пропущен в последнем unione.


да и еще вы пропустили один join LEFT JOIN profile_values pv ON pv.uid = cof.courier_id
 
 Top
biperch
Отправлено: 11 Июня, 2013 - 23:45:25
Post Id



Частый посетитель


Покинул форум
Сообщений всего: 588
Дата рег-ции: Окт. 2009  
Откуда: Днепропетровск


Помог: 8 раз(а)
вот explain
Прикреплено изображение (Нажмите для увеличения)
Снимок экрана - 11.06.2013 - 23:37:01.png
 
 Top
Champion Супермодератор
Отправлено: 12 Июня, 2013 - 12:31:12
Post Id



Активный участник


Покинул форум
Сообщений всего: 4350
Дата рег-ции: Авг. 2008  
Откуда: Москва


Помог: 57 раз(а)
biperch пишет:
еще вы пропустили один join LEFT JOIN profile_values pv
Тоже верно. Можно попробовать так
CODE (SQL):
скопировать код в буфер обмена
  1.  
  2. ... CROSS JOIN (
  3.         SELECT DISTINCT fid FROM profile_fields WHERE name = 'profile_last_name'
  4. ) t
  5.  LEFT JOIN profile_values pv ON pv.uid = cof.courier_id   AND pv.fid = t.fid
  6.  
И создать для этого один индекс по pv.uid и pv.fid вместе.
Еще, как говорили, подзапрос к profile_fields можно выполнить заранее и подставить полученные значения. Но это нехорошо, если он может возвращать большое количество записей.
Еще у меня все-таки есть предположение, что большинство лефл джойнов могут быть иннер. Это должно позволить уменьшить число 4636 из експлейна.

А вообще, если вы приложите дампчик, я с интересом поэксперементирую.
 
 Top
Поиск в теме | Версия для печати
Страниц (1): [1]
Сейчас эту тему просматривают: 0 (гостей: 0, зарегистрированных: 0)
« SQL и Архитектура БД »


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



Powered by PHP  Powered By MySQL  Powered by Nginx  Valid CSS  RSS

 
Powered by ExBB FM 1.0 RC1. InvisionExBB