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

Ответ

Использовать курсоры и любые циклы при работе с 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 в прайсах и менять только записи для которых в БД изменились какие либо цены с момента последнего обновления. Или вообще менять средние цены автоматом в триггере на изменение таблицы с прайсами.