Autor Zpráva
jeltom
Profil *
#1 · Zasláno: 17. 3. 2022, 14:45:25
Odpovědět Citovat
Zdravím,

chtěl bych se poradit, vypisuji z databáze produkty - snažím se zjistit cenu OD-DO v dané kategorii, případně filtrů.
Jde o to, že tam navíc zjišťuji sklad (total_stock>0) tj pouze produkty s větším počet ks skladem.

jenže produkt může mít varianty, proto to vyhledává product_id a product_parent_id.

dokázal by prosím někdo poradit, jak to vymyslet lépe? 

SELECT MIN(d1.price) as minProductPrice, MAX(d1.price) as maxProductPrice FROM ( 
    SELECT pr.id, pr.price AS price, (
            SELECT SUM(prstock.amount) FROM products_stock prstock 
            LEFT JOIN products npr ON prstock.product_id=npr.id 
            WHERE npr.deleted=0 AND (prstock.product_id=pr.id OR prstock.product_parent_id=pr.id) 
    ) as total_stock FROM products pr  
    LEFT JOIN products_category cat ON pr.id = cat.product_id  
    WHERE pr.active=1 AND pr.parent_id=0 AND pr.deleted=0 AND cat.category_id=130 
    GROUP BY pr.id 
) d1

pomalá je část (1,39s) 
AND (prstock.product_id=pr.id OR prstock.product_parent_id=pr.id)
Kajman
Profil
#2 · Zasláno: 18. 3. 2022, 08:09:03
Odpovědět Citovat
A potřebujete přesný výpočet total_stock nebo stačí jen rozlišení, zda je tam alespoň jeden kus?

Jaké jsou struktury tabulek včetně primárních klíčů a indexů?
jeltom
Profil *
#3 · Zasláno: 18. 3. 2022, 08:20:36
Odpovědět Citovat
potřeboval bych vědět rovnou kolik je skladem, protože z toho zobrazuji počet kusů skladem, a tím bych musel v while udělat další dotaz na znovu zjištění skladu.

zde je explain, rozdělil jsem - 

id - select_type - table - type - possible_keys - key - key_len - ref - rows - extra
1 - PRIMARY - <derived2> - ALL - NULL - NULL - NULL - NULL - 3395
2 - DERIVED - pr - index - PRIMARY,category_inventory,active,deleted - PRIMARY - 4 - NULL - 3872 - Using where
2 - DERIVED - cat - ref|filter - category_id,product_id,category_id_2 category_id|category_id_2 - 5|5 - pr.id - 2 (16%) - Using where; Using rowid filter
3 - DEPENDENT SUBQUERY - prstock - ALL - product_parent_id,product_id - NULL - NULL - NULL - 3823 - Using where
3 - DEPENDENT SUBQUERY - npr.eq_ref -  PRIMARY,deleted - PRIMARY  -  4 - prstock.product_id   - 1  - Using where
Kajman
Profil
#4 · Zasláno: 18. 3. 2022, 08:22:15
Odpovědět Citovat
Jaké jsou struktury tabulek včetně primárních klíčů a indexů?
jeltom
Profil *
#5 · Zasláno: 18. 3. 2022, 09:04:25 · Upravil/a: jeltom o 36 minut později
Odpovědět Citovat
posílám show create table 

CREATE TABLE `products` (
 `id` int(20) NOT NULL AUTO_INCREMENT,
 `parent_id` int(20) DEFAULT 0,
 `date_add` int(20) DEFAULT 0,
 `active` int(20) DEFAULT 0,
 `unit_id` int(5) DEFAULT 0,
 `guarantee_id` int(5) DEFAULT 0,
 `ean_code` varchar(30) DEFAULT NULL,
 `plu_code` varchar(30) DEFAULT NULL,
 `own_code` varchar(250) DEFAULT NULL,
 `guid_code` varchar(250) DEFAULT NULL,
 `dph_id` int(5) DEFAULT 0,
 `price` float DEFAULT NULL,
 `price_usd` float DEFAULT NULL,
 `price_eur` float DEFAULT NULL,
 `sale` int(2) DEFAULT 0,
 `price_before_sale` float DEFAULT 0,
 `price_usd_before_sale` float DEFAULT NULL,
 `price_eur_before_sale` float DEFAULT NULL,
 `sp_top` int(1) DEFAULT 0,
 `sp_new` int(1) DEFAULT 0,
 `deleted` int(20) DEFAULT 0,
 `buy_price` float DEFAULT NULL,
 `enable_buy` int(1) DEFAULT 1,
 `weight` varchar(20) DEFAULT '1',
 `height` varchar(20) DEFAULT '1',
 `width` varchar(20) DEFAULT '1',
 `length` varchar(20) DEFAULT '1',
 `min_buy` float DEFAULT 1,
 `archive` int(20) DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `category_inventory` (`parent_id`),
 KEY `active` (`active`),
 KEY `deleted` (`deleted`),
 KEY `archive` (`archive`)
) ENGINE=InnoDB AUTO_INCREMENT=9543 DEFAULT CHARSET=utf8
                
                
                
CREATE TABLE `products_category` (
 `id` int(50) NOT NULL AUTO_INCREMENT,
 `product_id` int(5) DEFAULT 0,
 `category_id` int(20) DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `category_id` (`product_id`),
 KEY `product_id` (`product_id`),
 KEY `category_id_2` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16182 DEFAULT CHARSET=utf8
                

CREATE TABLE `products_stock` (
 `id` int(100) NOT NULL AUTO_INCREMENT,
 `product_id` int(20) NOT NULL,
 `variant_id` int(10) DEFAULT 0,
 `stock_id` int(100) DEFAULT 1,
 `availability_id` int(10) DEFAULT 1,
 `unit_key` varchar(100) DEFAULT '',
 `last_edit` int(20) DEFAULT 0,
 `amount` int(100) DEFAULT 0,
 `unlimited` int(1) DEFAULT 0,
 `product_parent_id` int(20) DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `product_parent_id` (`product_parent_id`),
 KEY `product_id` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3824 DEFAULT CHARSET=utf8



Všiml jsem si a upravil jsem ty indexy u products.category_inventory a products_category.category_id a category_id_2 vymazal


Napadlo mě ještě napsat to takhle, je to podstatně rychlejí, při počtu záznamech 0,07s 

SELECT MIN(d1.price) as minProductPrice, MAX(d1.price) as maxProductPrice, SUM(total_stock_pr+total_stock_var) as total_stock FROM ( 
    SELECT pr.id, pr.price AS price, (
            SELECT SUM(prstock.amount) FROM products_stock prstock 
            LEFT JOIN products npr ON prstock.product_id=npr.id 
            WHERE npr.deleted=0 AND prstock.product_id=pr.id 
    ) as total_stock_pr, (
            SELECT SUM(prstock.amount) FROM products_stock prstock 
            LEFT JOIN products npr ON prstock.product_id=npr.id 
            WHERE npr.deleted=0 AND prstock.product_parent_id=pr.id 
    ) as total_stock_var FROM products pr   
    LEFT JOIN products_category cat ON pr.id = cat.product_id  
    WHERE pr.active=1 AND pr.parent_id=0 AND pr.deleted=0 AND cat.category_id=130 
    GROUP BY pr.id 
) d1



PS - špatně jsem to napsal.
Tento příkaz zrovna mi jen vrací produkty, kde potřebuji vědět v případě filtru "pouze skladem" že jsou skladem (je jedno, kolik ks, prostě dle filtru kdy doplňuji pak do where)
Zde neřeším počet.

Počet řeším při jiném SQL, kde vypisuji produkty a potřebuji u nich znát sklad, ale SQL je podobný.
Kajman
Profil
#6 · Zasláno: 18. 3. 2022, 10:13:02
Odpovědět Citovat
Pozor na sčítání součtů, kde nebyl záznam a je tedy null hodnota. Bez ošetření nebude výsledek total_stock správný. Jistější tedy bude 
SUM(coalesce(total_stock_pr,0)+coalesce(total_stock_var,0))
Také součet nevyjde přesně, pokud by byly záznamy, kde prstock.product_id=prstock.product_parent_id.

LEFT u všech 3 joinů nemá význam, protože podle druhé tabulky vždy filtrujete v podmínce where. Odstranil bych ho.

U součtu total_stock_pr nemusíte join vůbec dělat, protože smazané záznamy vyfiltrujete díky pr.deleted=0.

Když nepotřebujete přesný počet, může někdy zrychlit dotaz podmínka, že tam je alespoň jeden záznam
dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html
ale pokud si evidujete v products_stock všechny pohyby (máte tedy plus a mínus změny) a neupravujete záznamy (což by dle primárního klíče vypadalo), stejně to budete muset sečít. Až tam budete mít ale miliony záznamů, tak asi i pro jiné dotazy si budete muset udržovat pomcnou tabulku, kde budou dle pohybů skladové počty předpočítány např. triggery nad tabulkou products_stock.

Vypadá to, že nevíte, co znamená v mysql číslo v závorce za typem sloupce int. Mrkněte hned na první odstavec plus odkaz z něho
dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html
Časová prodleva: 2 roky

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm:

0