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]   

> Описание: выполняется 30 секунд...
Поиск в теме | Версия для печати
NeuroZ
Отправлено: 13 Сентября, 2016 - 09:57:17
Post Id



Посетитель


Покинул форум
Сообщений всего: 393
Дата рег-ции: Апр. 2012  


Помог: 2 раз(а)
На сайте 15к товаров
Есть таблица с товаром
Id и десяток полей

Есть таблица с ценами
Id, ItemID, и пяток полей

Есть таблица со складом
Id, ItemID и пяток полей

Есть таблица связей товар-категория
Id, ItemID, CatID

Задача тривиальная: вывести определенное кол-во товара на странице категории.

Запрос:
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT item.name, item.sku, item.image, item.manufacturer, stor.count, pr.price, pr.currency
  2. FROM items AS item
  3. LEFT JOIN prices AS pr ON pr.itemID = item.id
  4. LEFT JOIN storage AS stor ON stor.itemID = item.id
  5. LEFT JOIN items_cats AS cat ON cat.itemID = item.id
  6. WHERE cat.catID = 1
  7. ORDER BY item.name ASC LIMIT 0, 30;
 
 Top
Мелкий Супермодератор
Отправлено: 13 Сентября, 2016 - 11:29:31
Post Id



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


Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009  
Откуда: Россия, Санкт-Петербург


Помог: 618 раз(а)
explain где? Какая СУБД?

NeuroZ пишет:
LEFT JOIN items_cats AS cat ON cat.itemID = item.id
WHERE cat.catID = 1

Логическая ошибка. Делаете inner join, а написано left join. Может сбивать с толку оптимизатор.

На мой взгляд пользователя выводить на страницу категории произведение цен на склады как-то странно. Да и, кажется, это слабо совпадает с реальностью. На каждом складе есть каждая цена? Зачем тогда знать про склады? Надо тогда знать про сумму всего. Или там 1:1 связь? Зачем тогда отдельные таблицы?
И по общему дизайну в 3 из 4 таблицах поле id нафиг не нужно. Если, правда только, это не обязывает делать какая-нибудь глупая orm.

И ещё взгляд пользователя - товары без цен реально надо выводить в каталоге?


-----
PostgreSQL DBA
 
 Top
NeuroZ
Отправлено: 13 Сентября, 2016 - 12:13:50
Post Id



Посетитель


Покинул форум
Сообщений всего: 393
Дата рег-ции: Апр. 2012  


Помог: 2 раз(а)
Мелкий пишет:
Логическая ошибка. Делаете inner join, а написано left join. Может сбивать с толку оптимизатор.

На мой взгляд пользователя выводить на страницу категории произведение цен на склады как-то странно. Да и, кажется, это слабо совпадает с реальностью. На каждом складе есть каждая цена? Зачем тогда знать про склады? Надо тогда знать про сумму всего. Или там 1:1 связь? Зачем тогда отдельные таблицы?
И по общему дизайну в 3 из 4 таблицах поле id нафиг не нужно. Если, правда только, это не обязывает делать какая-нибудь глупая orm.

И ещё взгляд пользователя - товары без цен реально надо выводить в каталоге?

1. Заменил всё на INNER JOIN (уменьшилось время до 26 секунд)
2. Фактически в складах хранится кол-во конкретного товара и минимальная единица заказа
3. Технически таблицу склада и цен можно объединить в одну (разделены они только из соображения логики, именно поэтому отдельные таблицы)
4. По поводу того, что не нужно поле id в 3 из 4 таблицах - согласен. По привычке поставил примари кей с автоинкрементом.
5. "Товары без цен в каталоге?" - наоборот как раз.. Цены и кол-во товаров нужны на странице списка товаров.

В виду особенностей данных и дизайна сайта - отсутствует страница "карточка товара". Т.е. пользователь может добавить товар в корзину прямо со страницы списка товаров.

Так как тогда лучше сделать?
-Объединить таблицу склада и цен? (по замерам выигрываем в скорости, снижая ее до 7 секунд)
-Какие еще действия можно произвести?

(Отредактировано автором: 13 Сентября, 2016 - 12:19:15)

 
 Top
Мелкий Супермодератор
Отправлено: 13 Сентября, 2016 - 13:51:29
Post Id



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


Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009  
Откуда: Россия, Санкт-Петербург


Помог: 618 раз(а)
NeuroZ пишет:
1. Заменил всё на INNER JOIN (уменьшилось время до 26 секунд)

NeuroZ пишет:
5. "Товары без цен в каталоге?" - наоборот как раз.. Цены и кол-во товаров нужны на странице списка товаров.

Это вопросы к задаче и осмысленности использования inner или left join.
Если цены товара в таблице цен нет - этот товар в случае left join всё равно будет выведен. Аналогично в случае склада. Это может быть ожидаемым поведением.

NeuroZ пишет:
2. Фактически в складах хранится кол-во конкретного товара и минимальная единица заказа
3. Технически таблицу склада и цен можно объединить в одну (разделены они только из соображения логики, именно поэтому отдельные таблицы)

Не пониманию требований и ограничений вашей предметной области. Но перемножать цены на склады мне по-прежнему кажется странной идеей, почему 5 складов и 3 цены должны выводиться как 15 позиций в каталоге?

NeuroZ пишет:
В виду особенностей данных и дизайна сайта - отсутствует страница "карточка товара". Т.е. пользователь может добавить товар в корзину прямо со страницы списка товаров.

Всё равно с точки зрения пользователя мне не нравится куча идентичных предложений. А у вас они элементарно группируются и лишь искусственно множатся в интерфейсе. Пример интерфейса: описание товара, минимакс цен, информацию о количестве, хинт с расшифровкой где и по какой цене доступно. Над кнопкой заказа подумать. Можно модальным окном, можно slidedown с таблицей склад-цена-количество-купить (короткие списки можно и сразу держать открытыми).

Ещё раз, explain где? И какая СУБД? Хотя limit, offset через запятую - вроде бы только mysql. Но не претендую на знание всех диалектов, может и что-то другое ещё есть.


-----
PostgreSQL DBA
 
 Top
NeuroZ
Отправлено: 14 Сентября, 2016 - 09:50:36
Post Id



Посетитель


Покинул форум
Сообщений всего: 393
Дата рег-ции: Апр. 2012  


Помог: 2 раз(а)
Мелкий пишет:
Это вопросы к задаче и осмысленности использования inner или left join.
Если цены товара в таблице цен нет - этот товар в случае left join всё равно будет выведен. Аналогично в случае склада. Это может быть ожидаемым поведением.

Да, да, я понял в чем суть этих запросов. И Вы правы, мне именно INNER JOIN нужен.
Мелкий пишет:
Не пониманию требований и ограничений вашей предметной области. Но перемножать цены на склады мне по-прежнему кажется странной идеей, почему 5 складов и 3 цены должны выводиться как 15 позиций в каталоге?

В каком месте у меня происходит перемножение?
Мелкий пишет:
Ещё раз, explain где? И какая СУБД? Хотя limit, offset через запятую - вроде бы только mysql. Но не претендую на знание всех диалектов, может и что-то другое ещё есть.

MariaDB
Explain:
Спойлер (Отобразить)

Объединил 2 таблицы (цен и склада), убрал лишние колонки id. Получил время выполнения 0.061s (вполне удовлетворительно).
Текущий SQL практически не изменился (убрана 1 таблица из запроса)
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT item.name, item.sku, item.image, item.manufacturer, pr.count, pr.price, pr.currency
  2. FROM items AS item
  3. INNER JOIN prices AS pr ON pr.itemID = item.id
  4. INNER JOIN items_cats AS cat ON cat.itemID = item.id
  5. WHERE cat.catID = 1
  6. ORDER BY item.name ASC LIMIT 0, 30;

(Добавление)
Спасибо, за замечания)) они навели на правильное решение касательно запросов и лишних полей)
 
 Top
Мелкий Супермодератор
Отправлено: 14 Сентября, 2016 - 09:57:38
Post Id



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


Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009  
Откуда: Россия, Санкт-Петербург


Помог: 618 раз(а)
NeuroZ пишет:
В каком месте у меня происходит перемножение?

Вот здесь было:
NeuroZ пишет:
LEFT JOIN prices AS pr ON pr.itemID = item.id
LEFT JOIN storage AS stor ON stor.itemID = item.id

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

NeuroZ пишет:
Получил время выполнения 0.061s

Вот так больше похоже, ага.


-----
PostgreSQL DBA
 
 Top
NeuroZ
Отправлено: 14 Сентября, 2016 - 11:02:42
Post Id



Посетитель


Покинул форум
Сообщений всего: 393
Дата рег-ции: Апр. 2012  


Помог: 2 раз(а)
Мелкий пишет:
Между собой таблицы никак не связаны, но при этом содержат не строго 1 или 0 строк с этим itemID

Я так понял - это определяется путем установки поля в primary key ?
 
 Top
Мелкий Супермодератор
Отправлено: 14 Сентября, 2016 - 11:41:25
Post Id



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


Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009  
Откуда: Россия, Санкт-Петербург


Помог: 618 раз(а)
Nested loop inner join на пальцах:

PHP:
скопировать код в буфер обмена
  1. /*
  2. select * from items
  3.  join prices using(item)
  4.  join storages using(item)
  5. */
  6.  
  7. function innerJoinNestedLoop(array $leftRow, array $rightTable, $field)
  8. {
  9.     foreach ($rightTable as $row) {
  10.         if (! array_key_exists($field, $row)) {
  11.             continue;
  12.         }
  13.         if ($row[ $field ] === $leftRow[ $field ]) {
  14.             yield array_merge($leftRow, $row);
  15.         }
  16.     }
  17. }
  18.  
  19. $items = [
  20.     ['item' => 1],
  21.     ['item' => 2],
  22.     ['item' => 3],
  23. ];
  24. $prices = [
  25.     [ 'item' => 1, 'price' => '5 р.'],
  26.     [ 'item' => 1, 'price' => '6 р.'],
  27.     [ 'item' => 1, 'price' => '7 р.'],
  28.     [ 'item' => 2, 'price' => '1 септим'],
  29.     [ 'item' => 2, 'price' => '15 септим'],
  30.     [ 'item' => 6, 'price' => '6 дрейков'],
  31. ];
  32. $storages = [
  33.     [ 'item' => 1, 'storage' => 'спб'],
  34.     [ 'item' => 1, 'storage' => 'мск'],
  35.     [ 'item' => 2, 'storage' => 'Морнхолд'],
  36.     [ 'item' => 4, 'storage' => 'Вивек'],
  37. ];
  38.  
  39. foreach ($items as $itemRow) {
  40.     foreach (innerJoinNestedLoop($itemRow, $prices, 'item') as $itemPriceRow) {
  41.         foreach (innerJoinNestedLoop($itemPriceRow, $storages, 'item') as $resultRow) {
  42.             echo json_encode($resultRow, JSON_UNESCAPED_UNICODE),PHP_EOL;
  43.         }
  44.     }
  45. }

Понятно, почему в результате 8 строк, из которых 6 про item = 1?


-----
PostgreSQL DBA
 
 Top
NeuroZ
Отправлено: 14 Сентября, 2016 - 11:56:55
Post Id



Посетитель


Покинул форум
Сообщений всего: 393
Дата рег-ции: Апр. 2012  


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


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



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

 
Powered by ExBB FM 1.0 RC1. InvisionExBB