Вибір товарів, в яких залишок по опціях = 0

[remix8080]

Вітаю.

Спекотно, підтуплюю (

Допоможіть, будь ласка, скласти запит по вибірці з таблиці oc_product всіх product_id, для яких в oc_product_option_value залишок всіх опцій рівний 0 (quantity=0).

Наперед дякую!

[remix8080]

Пробую так (поки з таблиці oc_product_option_value):

SELECT prov.product_id, prov.quantity FROM oc_product_option_value prov GROUP BY prov.product_id, prov.quantity HAVING MAX(prov.quantity)=0 ORDER BY 1

але попадаються товари, в яких є ненульові залишки опцій (

Змінено 27 червня 2022 користувачем remix8080

[remix8080]

Питання знято.

SELECT prov.product_id FROM oc_product_option_value prov GROUP BY prov.product_id HAVING MAX(prov.quantity)=0 ORDER BY 1

 

[remix8080]

Вітаю.
Допоможіть, будь ласка, прискорити запит.
Потрібно в таблиці oc_product обнулити залишки товарів, для яких залишок опцій рівний 0.
При кількості записів ~15К в oc_product і ~3,5К в oc_product_option_value запит

UPDATE `oc_product` pr SET pr.quantity=0 WHERE pr.product_id IN 
(SELECT prov.product_id FROM `oc_product_option_value` prov GROUP BY prov.product_id HAVING MAX(prov.quantity)=0);

виконується кілька хвилин - якось нефеншуйно (

[remix8080]

Специ, підкажіть, як можна оптимізувати/прискорити цей запит? Виконується більше 3 хв (

UPDATE `oc_product` pr SET pr.quantity=0 WHERE pr.product_id IN 
(SELECT prov.product_id FROM `oc_product_option_value` prov GROUP BY prov.product_id HAVING MAX(prov.quantity)=0);

Сам селект

SELECT prov.product_id FROM `oc_product_option_value` prov GROUP BY prov.product_id HAVING MAX(prov.quantity)=0

виконується практично миттєво.

[nogocuHoBuk]

Такий запит буде швидше, (при наймні в данному випадку):
 

SELECT DISTINCT prov.product_id FROM `oc_product_option_value` prov WHERE `quantity` = 0

аніж Ваш 

 

SELECT prov.product_id FROM `oc_product_option_value` prov GROUP BY prov.product_id HAVING MAX(prov.quantity)=0

 

А час виконання апдейту залежіть, мені здається, від того, яку кількість product_id повертає попередній запит.

Тому в UPDATE краще зменьшити кількість апдейтів, додавши в запит перевірку на кількість.

UPDATE `oc_product` pr SET pr.quantity=0 WHERE pr.quantity > 0  AND pr.product_id IN 
(SELECT DISTINCT prov.product_id FROM `oc_product_option_value` prov WHERE `quantity` = 0);

Таким чином не будуть апдейтітись поля, в яких ВЖЕ кількість дорівнює 0.

[remix8080]

03.07.2022 в 01:02, nogocuHoBuk сказал:

Такий запит буде швидше, (при наймні в данному випадку):
 

SELECT DISTINCT prov.product_id FROM `oc_product_option_value` prov WHERE `quantity` = 0

аніж Ваш 

 

Не варіант. Цей видасть товари, в яких ХОЧА Б ОДНА опція має залишок 0. А я вибираю ВСІ такі товари. Та й швидість цього запиту практично не береться до уваги. Результат видає не багато, ~ 200 товарів.

 

 

 

03.07.2022 в 01:02, nogocuHoBuk сказал:

Тому в UPDATE краще зменьшити кількість апдейтів, додавши в запит перевірку на кількість.

UPDATE `oc_product` pr SET pr.quantity=0 WHERE pr.quantity > 0  AND pr.product_id IN 
(SELECT DISTINCT prov.product_id FROM `oc_product_option_value` prov WHERE `quantity` = 0);

Таким чином не будуть апдейтітись поля, в яких ВЖЕ кількість дорівнює 0.

Перевірку не додавав, думав, не дасть приросту. Але все-таки при pr.quantity>0 зменшився час з 210с до 160с. Вже результат )

 

П.С. Вдячний за відповідь.

[nogocuHoBuk]

03.07.2022 в 01:41, remix8080 сказал:

А я вибираю ВСІ такі товари.

Так. То вже я тупанув.
 

 

03.07.2022 в 01:41, remix8080 сказал:

Перевірку не додавав, думав, не дасть приросту. Але все-таки при pr.quantity>0 зменшився час з 210с до 160с. Вже результат )

При повторному запиті має зрости до 0. (звісно залежить від часу виконання SELECT'а)
Тому краще повісити на крон раз в день/12 годин/годину в залежності від кількості продаж. Може раз на день, а може й раз на годину  При періодичному виконанні кількість апдейтів буде стабільно мала.

[remix8080]

Всі наступні запити також виконуються такий самий час (~160с).

П.С. Саме на крон і планую згодом повісити деякий "пучок" запитів.

[nogocuHoBuk]

03.07.2022 в 01:54, remix8080 сказал:

такий самий час (~160с).

додайте oc_product_quantity до индексу
 

ALTER TABLE `oc_product` ADD INDEX(`quantity`)

І насолоджуйтесь  
Бо перший запит апдейта до кількості. Дайте змогу вибрати значення миттєво (в переносному сенсі, звісно). 
Навіть при 15к записів навіть 160 секунд це ДУЖЕ багато.
 

[remix8080]

Індекс додав. Час не змінився.

Та то вже таке...

 

Ще раз вдячний за допомогу!

[nogocuHoBuk]

03.07.2022 в 02:07, remix8080 сказал:

Індекс додав. Час не змінився.

Це все "Stranger Things"

[nogocuHoBuk]

Знайшов рішення
 

UPDATE `oc_product` pr SET pr.quantity=0 WHERE pr.product_id IN 
	(
	SELECT prod_id FROM 
			(
			SELECT prov.product_id AS prod_id FROM `oc_product_option_value` prov 
			LEFT JOIN oc_product op 
			ON op.product_id = prov.product_id 
			WHERE op.quantity > 0 
			GROUP BY prov.product_id HAVING MAX(prov.quantity)=0
			) 
	AS pid
	)

Задля наочності написав у кілька рядків.
Суть полягає в тому, що SELECT вибирає лише ті product_id для IN, де кількіть в oc_product більше 0
Ну і таке рішення само по собі буде швидше.

Ваше перше рішення: 1.37 секунд
 

Скрытый текст

Мій перший варіант, з додаванням перевірки кількості: 0.79 секунд
 

Скрытый текст

Остаточне рішення: 0.05 секунд
 

Скрытый текст

Нарешті можу і в люлю)

[chukcha]

откажитесь от IN
Используйте JOIN

 

[remix8080]

03.07.2022 в 02:53, nogocuHoBuk сказал:

Знайшов рішення
 

UPDATE `oc_product` pr SET pr.quantity=0 WHERE pr.product_id IN 
	(
	SELECT prod_id FROM 
			(
			SELECT prov.product_id AS prod_id FROM `oc_product_option_value` prov 
			LEFT JOIN oc_product op 
			ON op.product_id = prov.product_id 
			WHERE op.quantity > 0 
			GROUP BY prov.product_id HAVING MAX(prov.quantity)=0
			) 
	AS pid
	)

 

 

Брр, не знаю як, але відпрацювало практично моментально. Розбиратися/аналізувати вже буду завтра )

 

03.07.2022 в 02:53, nogocuHoBuk сказал:

Нарешті можу і в люлю)

Як в Петрика П'яточкіна. "Ура, 10 слоненят. Тепер я засну" )))

[remix8080]

03.07.2022 в 03:07, chukcha сказал:

откажитесь от IN
Используйте JOIN

 

Пробував, експериментував - не вийшло (

[chukcha]

03.07.2022 в 03:36, remix8080 сказал:

Пробував, експериментував - не вийшло (

покажіть спробу

[remix8080]

Доброго дня.

03.07.2022 в 10:37, chukcha сказал:

покажіть спробу

Якось так

UPDATE oc_product pr
INNER JOIN (SELECT prov.product_id FROM `oc_product_option_value` prov GROUP BY prov.product_id HAVING MAX(prov.quantity)=0)
ON pr.product_id = prov.product_id
SET pr.quantity = 0

 

[chukcha]

меня смущает
MAX
Зачем он вам если вам нужны опции с quantity 0

[remix8080]

Для одного товару з oc_product може бути кілька записів в oc_product_option_value, серед яких не всі quantity=0. Тому через MAX вибираю всі нульові.

[nogocuHoBuk]

03.07.2022 в 16:50, remix8080 сказал:

Тому через MAX вибираю всі нульові.

Мені здається (я навіть впевнений) що SUM() буде працювати швидше, ніж MAX()

SUM(prov.quantity)=0

 

[remix8080]

Думаю, не істотно, оскільки запит з MAX виконується практично моментально.

UPD. При великій кількості записів в oc_product_option_value - безперечно.

Змінено 3 липня 2022 користувачем remix8080

[nogocuHoBuk]

03.07.2022 в 16:53, remix8080 сказал:

Думаю, не істотно

КРщае перевірити
 

SELECT prov.product_id FROM `oc_product_option_value` prov GROUP BY prov.product_id HAVING MAX(prov.quantity)=0

а потім 
 

SELECT prov.product_id FROM `oc_product_option_value` prov GROUP BY prov.product_id HAVING SUM(prov.quantity)=0

Бо на моїх 100 записах час в межах похибки
У Вас точніше буде. 

[remix8080]

При ~ 3,5К записів в oc_product_option_value один і другий запит 0,0018с

[nogocuHoBuk]

03.07.2022 в 15:16, remix8080 сказал:

Якось так

Доречі. У Вас тут має бідкатися БД на те, що немає аліасів.
Потрібно якось так:

UPDATE oc_product pr
INNER JOIN (SELECT prov.product_id FROM `oc_product_option_value` prov GROUP BY prov.product_id HAVING MAX(prov.quantity)=0) AS per
ON pr.product_id = per.product_id
SET pr.quantity = 0
WHERE pr.quantity > 0

(додав ще й перевірку на кількість більше 0)
Але мені здається цей запит будет довшим за той, що я пропонував раніше.
Бо там для IN вибираються виключно ті product_id у яких кількість в oc_product більше 0.

В останньому ж запиті кількість записів для обробки значно більша.
PS. Ну і в такому вигляді потестуйте чи є різниця між SUM та MAX. То вже мені на майбутнє