MySQL процедуры для чайников

#php #mysql

Имею две таблицы с товарами интернет магазина. В одну попадают только товары от различных
поставщиков с указанием ID поставщика. Во вторую попадают все цены и наличие по каждому
товару первой таблице, по каждому складу поставщика.

Так как SQL недавно появился в моей жизни в таких масштабах, приходится просить помощи,
не во всем сам могу разобраться.

Кратко о данных в таблицах: 

Таблица с товарами выглядит вот так



Таблица с ценами так



Как видно на изображениях, на один товар может приходиться несколько различных цен
на различных складах. 
Написал процедуру для усреднения цен по складам каждого поставщика.

CREATE PROCEDURE `average`(IN `v_id` INT, IN `v_in_id` INT, IN `v_sup_id` INT)
BEGIN
  DECLARE vPrice INT;
  DECLARE vAmount INT;

  SET vPrice = (SELECT (SUM(buy_price*amount)/SUM(amount)) FROM td_prices WHERE buy_price
> 0 AND amount > 0 AND item_id = v_in_id and sup_id = v_sup_id);
  SET vAmount  = (SELECT SUM(amount) FROM td_prices WHERE item_id = v_in_id and sup_id
= v_sup_id);

  UPDATE td_items SET buy_price = vPrice, amount = vAmount WHERE id = v_id;
END;;


Не могу сказать что хорошо написал, опыта не хватает, возможно и заглючит при нулевой
цене или количестве, но пока работает.

Процедуру выполняю в PHP скрипте - делаю выборку всех id,in_id,sup_id из таблицы
items и подставляю их в мою процедуру, вызывая ее в цикле перебора этой самой выборки.
Грубо говоря

$data = $sql->get('SELECT * FROM td_items');
foreach($data as $item) { $sql->query('call avarage(_ids_from_$item_here)'); }


Да, идея банальная и не самая производительная..

После выполнения процедуры таблица заполняется вполне корректно



Собственно, вопрос: Сейчас на 10 тысяч имеющихся в таблице items записей выполнение
всего моего скрипта занимает аж 10 минут (на ноутбуке). Это никуда не годится, я прекрасно
понимаю что если объеденить foreach PHP и SQL в одной единой процедуре, процесс пойдет
намного быстрее. Но опыта не хватает. Подскажите, пожалуйста, методы оптимизации всей
этой "выборки". Второй день бьюсь с sql курсорами и переменными, результата и понимания 0 
    

Ответы

Ответ 1

Использовать курсоры и любые циклы при работе с SQL надо в очень редких, особо тяжелых,
случаях. Практически любую работу в SQL можно выполнить одним запросом. Ваш цикл по
td_prices и вызов процедуры для обновления каждой отдельной строки можно заменить таким
запросом:

UPDATE td_items I
  JOIN (SELECT item_id, sup_id,
               SUM(IF(buy_price > 0 AND amount > 0,buy_price*amount,0))
              /SUM(IF(buy_price > 0 AND amount > 0,amount,0)) as buy_price,
               SUM(amount) as amount
          FROM td_prices
         GROUP BY item_id, sup_id
       ) P
     ON I.in_id=P.item_id AND I.sup_id=P.sup_id
    SET I.buy_price=P.buy_price, I.amount=P.amount


IF пришлось применить т.к. у вас почему то используются немного разные выборки для
количества (берущие вообще все записи) и цен (берущие большие нуля записи).
Вообще стоит подумать о дополнительных признаках по которым решать какие записи надо
обновлять, а какие нет. Например вести поле timestamp в прайсах и менять только записи
для которых в БД изменились какие либо цены с момента последнего обновления. Или вообще
менять средние цены автоматом в триггере на изменение таблицы с прайсами.


Ответ 2

Подход, при котором ваш PHP-скрипт проходится по таблице и обновляет поле у каждой
записи, конечно, очень медленный. 10 минут еще не так много. Когда ваша таблица станет
больше, ждать придётся гораздо дольше.

А действительно ли вам нужно хранить поле со средней ценой? Если вы хотите получить
список товаров со средними ценами можно использовать запрос:

select 
    g.id, 
    /* остальные необходимые поля*/, 
    SUM(p.buy_price*p.amount)/SUM(p.amount), SUM(p.amount)
from 
    td_goods g
    join td_prices p on p.item_id = g.id
group by 
    g.id, /* остальные необходимые поля*/


Можно на основе запроса сделать view и будет представление исходной таблицы, но с
дополнительным полем содержащим усреднённую цену.