Autor Zpráva
Antonín
Profil *
#1 · Zasláno: 24. 10. 2022, 14:08:26 · Upravil/a: Antonín o minutu později
Odpovědět Citovat
Zdravím,

chtěl bych Vás poprosit o radu ohledně optimalizace, se kterou bojuji.
Mám přehled o produktech na několika obchodech/pobočkách.
Jedná se (zatím) zhruba o 100-200 tis produktů, ale bude těch záznamů více.

Mám 2 tabulky

1/ Tabulka: products
Tabulka s infem o produktech 
CREATE TABLE `products` (
 `id` int(20) NOT NULL AUTO_INCREMENT,
 `parent_id` int DEFAULT 0,
 `ean_code` char(13) DEFAULT NULL,
 `is_checked` tinyint(1) DEFAULT 0,
 `name` varchar(200) DEFAULT NULL,
 `short_description` text DEFAULT NULL,
 `description` longtext DEFAULT NULL, 
 `active` tinyint(1) DEFAULT 0,
 `deleted` tinyint(1) DEFAULT 0,
 `archive` tinyint(1) DEFAULT 0,
 `manufacturer_id` int(20) DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `ean_code` (`ean_code`),
 KEY `parent_id` (`parent_id`),
 KEY `deleted` (`deleted`),
 KEY `is_checked` (`is_checked`),
 KEY `archive` (`archive`),
 KEY `active` (`active`),
 KEY `manufacturer_id` (`manufacturer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=121362 DEFAULT CHARSET=utf8


2/ Tabulka: products_shops
V této tabulce jsou záznamy o produktech z obchodů - tj. 1 produkt zde může být i 10x pokud ho má 10 poboček.. 

CREATE TABLE `products_shops` (
 `id` int(20) NOT NULL AUTO_INCREMENT,
 `product_id` int(20) DEFAULT NULL,
 `ean` char(13) DEFAULT NULL,
 `price` float DEFAULT NULL,
 `shop_id` int(20) DEFAULT 0,
 `url` text DEFAULT NULL,
 `date_update` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `product_id` (`product_id`),
 KEY `shop_id` (`shop_id`),
 KEY `ean` (`ean`)
) ENGINE=InnoDB AUTO_INCREMENT=157222 DEFAULT CHARSET=utf8


A SQL:
Tento SQL mi vrátí všechny produkty, které jsou dostupné na nějaké pobočce. 

SELECT pr.id,pr.name,pr.urlname FROM products pr 
LEFT JOIN products_shops spr ON spr.product_id=pr.id 
WHERE pr.active=1 AND pr.parent_id=0 AND pr.deleted=0 AND pr.is_checked=1 
GROUP BY pr.id HAVING COUNT(spr.id) > 0 
ORDER BY pr.id DESC 
LIMIT 0, 20

Ale SQL trvá 0.45-0.60s, jakmile odeberu připojenou tabulku _shops, samozřejmě trvá 0.002s...


Pardon zadal jsem do špatné sekce
Kajman
Profil
#2 · Zasláno: 24. 10. 2022, 14:55:27
Odpovědět Citovat
Left join společně s having COUNT(spr.id) > 0 nedává smysl. Dejte jen join (vnitřní spojení mívá menší režii než vnější) a having pak nemusíte použít.

Pro takový dotaz, bych si udělal index na více sloupci, něco jako (active, deleted, is_checked, parent_id, id) - kdy pořadní prvních 4 sloupců bych zvolil dle dalších dotazů, které by z toho mohly čerpat. Je možné, že za pár let bude nutné dát smazané nebo nekompletní záznamy do další tabulky a v hlavní mít jen ty, co se mají zobrazovat (někdy se to řeší partitioningem). Je lepší si nyní vygenerovat soubor dat, které lépe odpovídají budoucímu stavu, a testovat rychlost dotazů i nad ním.

Při jinak nastaveném serveru dotaz padne, protože nemáte v group by uvedené všechny sloupce. Nebo groupujte už data z druhé taublky před připojením (ale to si nejsem jistý, jestli bude rychlejší) něco jako 
SELECT pr.id,
       pr.name,
       pr.urlname
FROM   products pr
       JOIN (SELECT DISTINCT product_id
             FROM   products_shops) spr
         ON spr.product_id = pr.id
WHERE  pr.active = 1
       AND pr.parent_id = 0
       AND pr.deleted = 0
       AND pr.is_checked = 1
ORDER  BY pr.id DESC
LIMIT  0, 20
Antonín
Profil *
#3 · Zasláno: 24. 10. 2022, 15:21:57 · Upravil/a: Antonín o 28 minut později
Odpovědět Citovat
Kajmane moc díky za rady, s tím joinem to je pravda no..

Zkoušel jsem ten Váš příkaz a je to rychlejší /0,98s/ - ty indexy mám takto, já s tím nijak dál nepracuji (zatím), jen je potřebujeme ty produkty vypsat

Ještě jsem tam doplnil filtr na kategorii a cenu + jeden příkaz na zjištění počtu produktů (pro stránkování) ten jeden jsem upravil podle Vás ale trvá 2,3s...
Vlastně jsem ho jen obalil do select count()..

Tabulka products_category: 

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 `product_id` (`product_id`),
 KEY `category_id` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=330676 DEFAULT CHARSET=utf8

příkaz na zjištění počtu produktů / kolem 2,3s 

SELECT COUNT(*) FROM ( 
                SELECT pr.id FROM products pr JOIN (
                                SELECT DISTINCT product_id FROM products_shops) spr ON spr.product_id = pr.id 
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 pr.is_checked = 1 AND cat.category_id=358 
) d1


hlavní SQL 

SELECT pr.id, pr.name, pr.urlname FROM products pr JOIN (
                SELECT DISTINCT product_id FROM products_shops) spr ON spr.product_id = pr.id 
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 pr.is_checked = 1 AND cat.category_id=358 
ORDER BY pr.id DESC 
LIMIT 0, 20


zjištění nejmenší a největší ceny pro filtraci od-do jsem měl takto, ale rovněž stojí na 0.5s 

                
SELECT MIN(d1.price) as minProductPrice, MAX(d1.price) as maxProductPrice FROM ( 
                SELECT pr.id, spr.price AS price FROM products pr 
                JOIN products_shops spr ON spr.product_id=pr.id
                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 pr.is_checked=1 AND cat.category_id=358 
                GROUP BY pr.id 
) d1


případně nevím jestli by šlo i sjednotit zjištění počtu produktů + min a max ceny v kategorii


A teď mi vlastně došlo, že já to tak použít nemohu, musím tam dát JOIN na products_shops, protože tam doplňuji filtr a řazení podle products_shops.price


Tedy ten příkaz s groupnutím před připojením nepoužiji, i když byl podstatně rychlejší


EDIT:

Ten hlavní SQL už běží za 0,02s, tak super.

Jediné co mám problém jsou ty 2 doplňkové, vyndal jsem ten LEFT a HAVING, ale oba jdou na 0,24-0,3s

0.2948129177s | CMD:
SELECT MIN(d1.price) as minProductPrice, MAX(d1.price) as maxProductPrice FROM ( SELECT pr.id, spr.price AS price FROM products pr JOIN products_shops spr ON spr.product_id=pr.id 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 pr.is_checked=1 AND cat.category_id=311 GROUP BY pr.id ) d1


0.2486248016s | CMD: 
    SELECT COUNT(*) FROM ( SELECT pr.id FROM products pr LEFT JOIN products_category cat ON pr.id = cat.product_id JOIN products_shops spr ON spr.product_id=pr.id WHERE pr.active=1 AND pr.parent_id=0 AND pr.deleted=0 AND pr.is_checked=1 AND cat.category_id=311 GROUP BY pr.id ) d1
Antonín
Profil *
#4 · Zasláno: 24. 10. 2022, 17:01:26
Odpovědět Citovat
EDIT 3:

Tak ještě změna, i hlavní SQL je stále pomalý např. v kategorii, kde je více produktů

0.4761891365s | CMD: 
SELECT pr.id,pr.name,pr.urlname FROM products pr 
JOIN products_shops spr ON spr.product_id=pr.id 
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 pr.is_checked=1 AND cat.category_id=308 
GROUP BY pr.id 
ORDER BY pr.id DESC LIMIT 0, 20
Stroganov
Profil *
#5 · Zasláno: 25. 10. 2022, 00:39:24
Odpovědět Citovat
Antonín:

Čo ukáže EXPLAIN pre daný select? Ak máte verziu 8, skúste rovno EXPLAIN ANALYZE, kde sú aj informácie o časoch.
Antonín
Profil *
#6 · Zasláno: 25. 10. 2022, 07:47:23
Odpovědět Citovat
Explain zde -

Kajman
Profil
#7 · Zasláno: 25. 10. 2022, 08:12:21
Odpovědět Citovat
Opět tam je nepotřebné vnější spojení. Nedávejte left u joinu, když pak z té tabulky máte podmínku ve where a musí být řádek z ní vždy připojený. Osobně bych si dal podmínku na kategorii přímo do on podmínky v joinu, ale na rychlost by to nemělo mít vliv.

Když se používá limit, tak max a min můžete počítat pomocí kolerovaných poddotazů jen pro těch 20 záznamů - to by mohlo být rychlejší.

Vícesloupcový index jste tedy nedělal? Zkuste ho, když tam nebude rozdíl v rychlosti, tak ho můžete zase smazat.
Antonín
Profil *
#8 · Zasláno: 25. 10. 2022, 08:23:04 · Upravil/a: Antonín o 4 minuty později
Odpovědět Citovat
S vícesloupcovým indexem nemám moc zkušenosti, právě teprve zkouším si hrát s připojováním tabulek....zkouším např.

Zobrazeny záznamy 0 - 19 (20 celkem, Dotaz trval 0.4991 sekund.) [id: 121399... - 121259...] 

SELECT pr.id,pr.name,pr.urlname FROM products pr 
JOIN products_category cat ON pr.id = cat.product_id AND cat.category_id=308
JOIN products_shops spr ON spr.product_id=pr.id
WHERE pr.active=1 AND pr.is_checked=1 AND pr.parent_id=0 AND pr.deleted=0
GROUP BY pr.id 
ORDER BY pr.id DESC LIMIT 0, 20



Ale 0,499s + select count na počet záznamu + max price a min price select mi dá i 1,5s a to je strašně hodně


Max a min price zjišťuji pro danou kategorii produktů, abych mohl mít nachystanou filtraci s min. cenou produktu v kategorii a max. cenou produktu, ne jen pro těch 20 záznamů, 20 záznamu je 1 stránka, pak je dalších 700...20,40. ...40,60
Kajman
Profil
#9 · Zasláno: 25. 10. 2022, 09:09:13
Odpovědět Citovat
Minimální a maximální cena ke kategorii bude asi pomalá, takže musíte využívat systém nějaké cache nebo předpočítaných hodnot v db, aby měl uživatel rychlé odezvy.
Antonín
Profil *
#10 · Zasláno: 25. 10. 2022, 10:46:50
Odpovědět Citovat
Ale pořád je rychlejší o polovinu než hlavní SQL, který bere 0,5s a je prakticky jednoduchý.
Časová prodleva: 6 dní
Antonín
Profil *
#11 · Zasláno: 31. 10. 2022, 08:27:11
Odpovědět Citovat
Zdravím, tak jsem testoval a vytvořil jsem multi-index 
sloupec-mohutnost
active-2    
deleted-1638    
is_checked-1552
parent_id-1552
id-88471
manufacturer_id-88471

a SQL s tisíci záznamy je 0,002s.

ještě pak vyřeším to, že zruším sloupec deleted a budu to přesouvat do jiné tabulky, ale teď mimo řeším ty 2 SQL, které mě trápí.

1/ Potřebuji zjistit celkový počet produktů (pro stránkování, abych věděl že v dané kategorii je 250 tis produktů s vybraným filtrem)

0,1s - 0,2s (dle objemu dat) 
                
SELECT COUNT(*) FROM ( 
                SELECT pr.id FROM products pr 
                JOIN products_category cat ON pr.id = cat.product_id AND cat.category_id=305 
                JOIN products_shops spr ON spr.product_id=pr.id 
                WHERE pr.active=1 AND pr.parent_id=0 AND pr.deleted=0 AND pr.is_checked=1 AND spr.price>2500 AND spr.price<14500
                GROUP BY pr.id 
) d1


2/ Pro filtr min a max. ceny (abych věděl ve vybrané kategorii, nebo kategorie + výrobce nejmenší a největší cenu)

0,2S - 0,3s 

SELECT MIN(d1.price) as minProductPrice, MAX(d1.price) as maxProductPrice FROM ( 
                SELECT pr.id, spr.price AS price FROM products pr 
                JOIN products_shops spr ON spr.product_id=pr.id 
                JOIN products_category cat ON pr.id = cat.product_id AND cat.category_id=305
                WHERE pr.active=1 AND pr.parent_id=0 AND pr.deleted=0 AND pr.is_checked=1 
                GROUP BY pr.id 
) d1


3/ Pokud jsem v nějaké kategorii, mám aktivní filtr pro kategorii/cenu, tak zjišťuji nově ještě (pro filtr) možné výrobce (seznam výrobců z DB výrobců)

trvá 0,15 - 0,2s 

SELECT lg.id,lg.name,lg.urlname,lg.img FROM products pr 
JOIN products_category cat ON pr.id = cat.product_id AND cat.category_id=305 
JOIN manufacturers lg ON pr.manufacturer_id = lg.id 
WHERE pr.active=1 AND pr.parent_id=0 AND pr.deleted=0 AND pr.is_checked=1  AND spr.price>2500 AND spr.price<4500
GROUP BY lg.id 
ORDER BY lg.name ASC


a celkem mi to, po vylepšení indexu trvá teď 0,5s, chtěl jsem se tedy poradit, jestli by jste neporadili s vylepšením dotazů na ten select/ minprice/ maxprice / případně jak to sjednotit do jednoho SQL (pokud to lze a je to např. lepší).
juriad
Profil
#12 · Zasláno: 2. 11. 2022, 11:07:34
Odpovědět Citovat
Nečetl jsem začátek, reaguji na poslední příspěvek.

Já se obecně snažím vyhnout group by, pokud skutečně není potřeba, a nahradit jej za semi join (existuje několik forem, já používám korelovaný exists; je možné použít i jednoduchý IN).

První dotaz bych přepsal na: 
select count(*)
from products pr
WHERE pr.active = 1
  AND pr.parent_id = 0
  AND pr.deleted = 0
  AND pr.is_checked = 1
  and exists(select 1 from products_category cat where cat.category_id = 305 and cat.product_id = pr.id)
  and exists(select 1 from products_shops spr where spr.product_id = pr.id and spr.price > 500 AND spr.price < 14500);

Druhý po podobném přepisu rychlejší bohužel není: 
SELECT MIN(spr.price) as minProductPrice, MAX(spr.price) as maxProductPrice
FROM products pr
         JOIN products_shops spr ON spr.product_id = pr.id
WHERE pr.active = 1
  AND pr.parent_id = 0
  AND pr.deleted = 0
  AND pr.is_checked = 1
  and exists(select 1 from products_category cat where pr.id = cat.product_id AND cat.category_id = 305);
Pozor na to, že tento dotaz vrací jiný výsledek, což je dáno tím, že tvůj group by vrátil nějakou jednu cenu ze všech shopů; můj uvažuje ceny ze všech shopů.

U třetího se mi vyplatilo použít distinct: 
with manus as (select distinct pr.manufacturer_id
            from products pr
            where pr.active = 1
              AND pr.parent_id = 0
              AND pr.deleted = 0
              AND pr.is_checked = 1
              and exists(select 1 from products_category cat where pr.id = cat.product_id AND cat.category_id = 305)
              and exists(select 1 from products_shops spr where spr.product_id = pr.id and spr.price > 2500 AND spr.price < 4500))
SELECT lg.id, lg.name, lg.urlname, lg.img
from manufacturers lg
         join manus on lg.id = manus.manufacturer_id
ORDER BY lg.name ASC;

Zkoušel jsem to na 1M produktů, 70% aktivních nesmazaných, každý ve dvou kategoriích, kde každá měla 2000 produktů. Každý produkt byl ve třech shopech s náhodným cenami. Dohromady bylo 1000 výrobců, každý s 1000 produkty.
Výše uvedené dotazy mi trvají kolem 3 sekund.
S indexem create index pc1 on products_category (category_id); pak byly všechny query rychlé, ale ty asi budeš mít katerorie různě velké. Máš třeba defaultní kategorii, která obsahuje všechny/většinu produktů? Možná by pak pomohly histogramy (od verze Mysql 8).

Třeba ti některé nápady budou užitečné.
Č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