Indexy SQL – mariadb

Ahoj, mám problém s výkonem dotazu. Mějme tabulku products kde je třeba milion řádků. select count(products_id) as total from products where products_status =  '1' and (products_quantity OR products_sklad); Chci najít produkty, které mají product_status = 1 a products_sklad nebo quantity > 0. Proč ten dotaz trvá přes 1 sekundu? Indexy mám products_id i status quantity a sklad. Ale asi blbě. Poradíte jak docílit toho, aby to bylo rychlé? Explain vrací toto: id? select_type? table? partitions? type? possible_keys? key? key_len? ref? rows? Extra?1 SIMPLE products NULL index_merge idx_products_status_zen,products_quantity,products_sklad products_quantity,products_sklad 4,4 NULL 537985 Using sort_union(products_quantity,products_sklad); Using where Díky.

můžeš ten explain dát v trochu čitelnější podobě, a tohle asi není celý explain, co?Každopádně z toho fragmentu jde vidět, že se použil index pro products_quantity,products_sklad a ten vrátil 0.5 milionů řádků, ty se musely projít jeden po druhém (jaký je výsledek toho count?). Chtělo by to ale vidět i zbytek explainu, podle mě by se mělo objevit více možností.Optimalizovat bys tohle měl tak, že budeš používat index na sloupci, který udělá co největší filtrovaci.Lze to třeba rozložit na jednotlivé dotazy (může být chybně, záleží jaký je vztah products_quantity a products_sklad):select sum(subtotal) as total from ( select count(products_id) as subtotal from products where products_status = '1' and products_quantity > 0 union all select count(products_id) as subtotal from products where products_status = '1' and products_sklad > 0) ta vytvořit dva indexy: products_status,products_quantity a products_status,products_sklad. Při range dotazech se totiž při multi sloupcovém indexu použije vždy jen ta první shoda pro první sloupec a druhá se musí dohledávat hrubou silou.

Díky, pomohlo to, trvá to teď 0,3s.  Potřebuju ten dotaz ještě rozšířit. Původní je takovéto: select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p2c.categories_id = ('791') and p.products_status =  '1' and (p.products_quantity OR p.products_sklad); Výsledek cca 380000. Zkouším něco jako: select sum(subtotal) as total from (   select count(products_id) as subtotal from products where products_status =  '1' and products_quantity > 0   UNION ALL   select count(products_id) as subtotal from products where products_status =  '1' and products_sklad > 0 UNION ALL select count(products_id) as subtotal from products_to_categories where categories_id = ('791') ) t Prostě produkty s nenulovým products_sklad, products_quantity a zároveň s odpovídajícím id kategorie. Toto vrací výsledek přes 1 000 000, správně je těch 380 000. products_id je ve všech třech tabulkách odpovídající a v rámci tabulky unikátní. Myslel jsem, že by se mi hodil INTERSECT, ale ten mi vůbec nefunguje.

ok, to už je nějaký posun. Jakou verzi mysql máš? Můžeš ukázat schéma tabulky (hlavně mě zajímá typ sloupce products_status) a definici indexů?Podmínka > 0 nebo != 0 je range dotaz. Jakýkoliv range dotaz nad indexem musí být vždy jako poslední sloupec v indexu. Podle explainu mi připadá, že máš indexy nadefinované jinak než (products_status, products_quantity), počet vrácených záznamů na řádku 2 explainu je totiž o dost vyšší než výsledek. Musíš si při optimalizaci sám zvážit, jestli je pro tebe levnější (časově) použít hrubou silu a temporary tabulku nebo to opřít o indexy, indexy obecně vždy lépe škálují na počet záznamů.Pokud je původní výsledek kolem 380 000 a upravený dotaz přes union all vrací 430 000, jsou tam duplicity, tj. některé produkty splňují obě podmínky, to se dá vyřešit, nebylo mi to z původního popisu jasné.Pokud chceš přidat ID kategorie, musíš to udělat do samotných dotazů, tj.:select count(products_id) as total from ( select products_id as subtotal from products where products_status = '1' and categories_id = '791' and products_quantity > 0 union select products_id as subtotal from products where products_status = '1' and categories_id = '791' and products_sklad > 0) tAbys mohl i přes indexy vyhledávat kategorii, musíš vytvořit nový složený indexy (categories_id,products_status,products_quantity) a (categories_id,products_status,products_sklad). Dal jsem na začátek indexu id kategorie, očekávám, že bude více selektivní než status produktu, je důležité v indexech co nejdříve vyfiltrovat co nejvíce záznamů, mysql si přeskládá podmínky podle pořadí indexů. Zároveň držím podmínku, že range dotaz na quantity,sklad je na konci. Zkus to a dej mi sem prosím explain, ať vidím změnu. Zároveň jsem tady zkusil vyřešit ten problém s jiným počtem záznamů (vracím product_id místo subtotal výpočtu).V samotným dotazu už toho pak není moc, co vylepšovat, vadí ten dvojitý range. Pokud jsou takovéhle dotazy pomalé v praxi, tak se to denormalizuje, ať už v aplikaci nebo pomocí triggeru při zápisu se vytvoří nový sloupec (či vazební tabulka), kde uděláš podmínku nějak takhle:if products_quantity > 0 and products_sklad > 0 then product_available = 1else product_available = 0Poté v se ti samotná dotaz výrazně zjednodušší na:select count(products_id) as total from products where products_status = '1' and categories_id = '791' and product_available = 1;k němu dodáš index (categories_id,product_available,products_status) a dotaz bude opřený pouze o indexy.Ještě bych rád řekl další poznámku, vidím u tebe podmínky typu categories_id = '791' nebo products_status = '1' , správně bys měl používat datový typ shodný se sloupcem, tj. pokud je sloupec int, mělo by to být categories_id = 791, products_status = 1. Tvoje podmínky jsou psané pro sloupce varchar/char. Je dobré tohle měnit, vyhneš se drahém přetypovávání.

Díky moc, vážím si tvojí podpory. V tomhle jsem začátečník. Problém je v tom, že to mám tak, že jeden produkt může být ve více kategoriích, takže tabulka products_to_categories  a tabulka zs_products je zvlášť. Prosím podívej se na ten plný původní dotaz, mám to tam jako p. a p2c: select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p2c.categories_id = ('791') and p.products_status =  '1' and (p.products_quantity OR p.products_sklad); Můžu udělat nějak index přes dvě tabulky? Zkoušel jsem vytvořit pohled, ale samozřejmě to je výkonově shodné s tím původním dotazem. Díky za tu poznámku k datovým typům, to určitě upravím. K tvým dalším dotazům: products_status má typ tinyint(1) Indexy na tabulce products: PRIMARY products_id INDEX products_date_added INDEX products_status INDEX products_date_available INDEX products_ordered INDEX products_price_sorter INDEX products_sort_order INDEX manufacturers_id INDEX master_categories_id INDEX products_ean INDEX products_ean_alternative INDEX products_image INDEX products_model INDEX products_ean INDEX products_price INDEX products_quantity INDEX products_sklad INDEX products_id, products_quantity, products_sklad, products_status INDEX products_id, products_status, products_quantity, products_sklad INDEX products_quantity, products_status INDEX products_sklad, products_status Indexy a struktura na tabulce products_to_categories: Sloupec Typ Komentář id int(11) Auto Increment products_id int(11) categories_id int(11) Indexy UNIQUE id INDEX id, products_id INDEX id, categories_id INDEX products_id, id INDEX products_id, categories_id INDEX products_id INDEX products_id, id, categories_id Verze: @@VERSION 10.1.48-MariaDB-0+deb9u2

já v tom jsem taky tak trochu věčný začátečník, pořád mám co se učit.Indexy nemohou být přes víc tabulek.Ty indexy máš špatně na tvůj typ dotazu, u indexu hooodně záleží na pořadí sloupců v něm, tj. indexy typu "INDEX products_id, products_quantity, products_sklad, products_status" nebo "INDEX products_id, products_status, products_quantity, products_sklad" či "INDEX products_quantity, products_status" jsou pro tyhle typy range query zbytečné. Musel bys přes ně hledat jen přesnou quantity nebo sklad.Ten dotaz s kategoriemi může vypadat třeba takhle:select count(products_id) as total from ( select products_id from products p join products_to_categories p2c using(products_id) where p2c.categories_id = 791 and p.products_status = 1 and p.products_quantity > 0 union select products_id from products p join products_to_categories p2c using(products_id) where p2c.categories_id = 791 and p.products_status = 1 and p.products_sklad > 0) tA potřebuješ k němu indexy:1) nad products_to_categories index (categories_id)2) nad products dva indexy (products_status, products_quantity) a (products_status, products_sklad)Když mi na tohle pošleš explain, tak ti zase dokážu říct, jestli jdeme správným směrem nebo ne.

products_to_categories: (indexy bez pravé strany jsou pravděpodobně nevyužitelné, na pravé straně je přísnější definice, pravděpodobně lepší pro explain)UNIQUE id PRIMARY idINDEX id, products_idINDEX id, categories_idINDEX products_id, idINDEX products_id, categories_id UNIQUE products_id, categories_id (pokud někdy zjišťujeme, do kterých kategorií je produkt zařazen) UNIQUE categories_id, products_id (zásadní vliv při omezování produktů patřících dané kategorii)INDEX products_id INDEX products_id, id, categories_idproducts: Bez informace o všech dotazech nelze indexy snadno posoudit. Obecně platí, že primary key nemá být součástí složeného indexu, když je to třeba, tak úplně vpravo. Složené indexy opět musí odpovídat dotazům.Podaří-li se všechny atributy výsledného dotazu najít v použitém indexu, budou dotazy rychlejší. Bude-li však nutné použít i jiné atributy, mohou (zbytečné) složené indexy zbytečně vytěžovat zdroje.Tedy, pokud je zjišťování počtu záznamů - viz příklad - stěžejní, může být index INDEX products_status, products_quantity, products_sklad užitečný. Primární klíč je implicitní součástí každého indexu, protože adresuje datový záznam.select count(1) as total from products where exists (select 1 from products_categories where products_categories.products_id=productes.products_id and products_categories.categories_id=791) and products.products_status = 1 and (products.products_quantity > 0 or products.products_sklad > 0)Pro úspěch tohoto dotazu je stěžejní index na products_categories: UNIQUE categories_id, products_id.Pomalé dotazy lze logovat pomocí nastavení:slow-query-log=1slow-query-log-file= /var/lib/mysql/mysql-slow-query.loglong-query-time=1

díky za popis, pár drobností bych ti jen upřesnil.

Napsal takatom;1645727

products: Bez informace o všech dotazech nelze indexy snadno posoudit. Obecně platí, že primary key nemá být součástí složeného indexu, když je to třeba, tak úplně vpravo. Složené indexy opět musí odpovídat dotazům.

na pravé straně musí být hlavně sloupce pro range dotazy, nedává žádný smysl dávat do indexu product_id nakonec, primární klíč má nejvíce entropie, měl by být na levé straně indexu, na konci je neefektivní. Ale obecně primární klíč ve složeném indexu není častý, možná tak jen pro unique.

Napsal takatom;1645727

Tedy, pokud je zjišťování počtu záznamů - viz příklad - stěžejní, může být index INDEX products_status, products_quantity, products_sklad užitečný. Primární klíč je implicitní součástí každého indexu, protože adresuje datový záznam.

Tohle nebude fungovat u range dotazů, jako v tomhle případě. U range dotazů mysql umí využít jen první range proti indexu, ten druhý dohledává ze záznamů. Je to způsobeno strukturou stromu v indexu.

Napsal takatom;1645727

Pro úspěch tohoto dotazu je stěžejní index na products_categories: UNIQUE categories_id, products_id.

unique je jen omezení při zápisu, kdy se vyhodnocuje, pro select dotazy se jedná o běžný index se vším všudy. V tomhle případě se ani nepoužije a mysql dá přednost primárním indexům, nelze totiž vyhodnotit spojení tabulek přes indexy (product_id) a (category_id, product_id), v tom složeném by musel být product_id na prvním místě.

Napsal TomášX;1645716

já v tom jsem taky tak trochu věčný začátečník, pořád mám co se učit.

Indexy nemohou být přes víc tabulek.

Ty indexy máš špatně na tvůj typ dotazu, u indexu hooodně záleží na pořadí sloupců v něm, tj. indexy typu "INDEX products_id, products_quantity, products_sklad, products_status" nebo "INDEX products_id, products_status, products_quantity, products_sklad" či "INDEX products_quantity, products_status" jsou pro tyhle typy range query zbytečné. Musel bys přes ně hledat jen přesnou quantity nebo sklad.

Ten dotaz s kategoriemi může vypadat třeba takhle:

select count(products_id) as total from (   select products_id   from products p   join products_to_categories p2c using(products_id)   where   p2c.categories_id = 791   and p.products_status =  1   and p.products_quantity > 0   union   select products_id   from products p   join products_to_categories p2c using(products_id)   where   p2c.categories_id = 791   and p.products_status =  1   and p.products_sklad > 0 ) t

A potřebuješ k němu indexy:

1) nad products_to_categories index (categories_id)

2) nad products dva indexy (products_status, products_quantity) a (products_status, products_sklad)

Když mi na tohle pošleš explain, tak ti zase dokážu říct, jestli jdeme správným směrem nebo ne.

Tak teď jsem někde na 2s, to není pořád dobrý: Proč tam je u products sklad pořád where? Indexy mám co se týče products_quantity a products_sklad takto: INDEX products_status, products_quantity INDEX products_status, products_sklad Napadá tě co je ještě šparně? Nebo na to mám jít přes trigger? Nebo si vytvořit sloupec products_avaiable a cronem jednou za minutu nastavovat 1, když bude products_status nebo products_quantity?---------- Příspěvek doplněn 31.05.2022 v 12:12 ----------

Napsal TomášX;1645716

já v tom jsem taky tak trochu věčný začátečník, pořád mám co se učit.

Indexy nemohou být přes víc tabulek.

Ty indexy máš špatně na tvůj typ dotazu, u indexu hooodně záleží na pořadí sloupců v něm, tj. indexy typu "INDEX products_id, products_quantity, products_sklad, products_status" nebo "INDEX products_id, products_status, products_quantity, products_sklad" či "INDEX products_quantity, products_status" jsou pro tyhle typy range query zbytečné. Musel bys přes ně hledat jen přesnou quantity nebo sklad.

Ten dotaz s kategoriemi může vypadat třeba takhle:

select count(products_id) as total from (   select products_id   from products p   join products_to_categories p2c using(products_id)   where   p2c.categories_id = 791   and p.products_status =  1   and p.products_quantity > 0   union   select products_id   from products p   join products_to_categories p2c using(products_id)   where   p2c.categories_id = 791   and p.products_status =  1   and p.products_sklad > 0 ) t

A potřebuješ k němu indexy:

1) nad products_to_categories index (categories_id)

2) nad products dva indexy (products_status, products_quantity) a (products_status, products_sklad)

Když mi na tohle pošleš explain, tak ti zase dokážu říct, jestli jdeme správným směrem nebo ne.

Díky, výsledek cca 2s, ale ten index  UNIQUE categories_id, products_id je podle mě i reálně k ničemu. Nemá to vliv na výkon dotazu. Navíc to podle mě ani nemůže být UNIQUE, když jeden produkt může mít více categories_id ...

odpovíd ti podrobněji večer, v první řadě, proč tam dáváš "p.product_sklad" místo "p.product_sklad > 0"? To generuje úplně rozdílný typ dotazu, ve složeném indexu by takový sloupec musel být na začátku a zároveň tím aktiviješ implicitní přetypování, což může generovat nestabilní výsledky. Je to vidět i na tom explainu, kdy nebyly použity správně indexy.Můžeš ještě zkusit tu moji variantu, ale "s > 0" u těch sloupců product_sklad a product_quantity? Schválně si pak porovnej explain, že i takhle drobná změna má dopad na běhový plán.

Nepomohl by WITH? Na velkých datech to pomáhá, když se jen počítají položky. Vyselektují se ID produktů ve WITH a v následném SELECTu se udělá COUNT, který ale běží nad mnohem menším množstvím dat, než když se dělá COUNT přímo v hlavním selectu nad všemi daty produktů. Potažmo, při selektech je vhodnější pracovat s měnší tabulkou jako hlavní (za FROM) a k ní připojovat větší tabulky (produkty za JOINem). Tím se omezí data, která je potřeba interně procházet a párovat. Něco na tenhle způsob (p.id nevím zda existuje, ale to si dopiš podle své struktury): WITH tempset (pid) AS (   SELECT p.id   FROM products_to_categories AS p2c   LEFT JOIN products AS p ON (p.id = p2c.products_id AND p2c.products_id != NULL)   WHERE     p2c.categories_id = 791     AND p.id = p2c.products_id     AND p.products_status =  1     AND p.products_quantity > 0     AND p.id != NULL     UNION     SELECT p.id   FROM products_to_categories AS p2c   LEFT JOIN products AS p ON (p.id = p2c.products_id AND p2c.products_id != NULL)   WHERE     p2c.categories_id = 791     AND p.id = p2c.products_id     AND p.products_status =  1     AND p.products_quantity > 0     AND p.id != NULL ) SELECT COUNT(DISTINCT pid) FROM tempset Úprava: nevím, zda ta tabulka products_to_categorie je menší, přehlédnul jsem se a myslel jsem, že je to tabulka kategorií, ne vazeb. V tomto případě bude produktová tabulka menší, takže patří za FROM, jak to bylo uvedené v příkladech výše. Pokud by se spojovala tabulka kategorií s produky přes relační tabulku vazeb, pak by byla ta tabulka kategorií za FROM a ostatní by se spojovalo přes LEFT JOINy.

with by pomoc mohl, musel by být ale napsaný nějak takhle:WITH filtered_products (products_id, products_quantity, products_sklad) AS (select p.products_id, p.products_quantity, p.products_sklad from products p join products_to_categories p2c on p.products_id = p2c.products_id where p2c.categories_id = 1 and p.products_status = 1)SELECT COUNT(DISTINCT products_id) from filtered_products where products_quantity > 0 or products_sklad > 0K tomu pak bude potřeba jen jediný index, na tabulce products_to_categories (products_id, categories_id). Zbytek skončí v temporary tabulce, mysql/mariadb neumí v tomhle případě použít derivované indexy jako to má třeba Oracle. Bude to efektivní, když na výstupu těch produktů nebude moc nebo budu mít velice rychle disky/dost paměti pro temporary tabulky. Tady je sice na výstupu hodně produktů, ale třeba to pomůže.U mariadb pak není rozdíl mezi variantouselect count(distinct products_id) as total from ( select p.products_id from products p join products_to_categories p2c on p.products_id = p2c.products_id where p2c.categories_id = 1 and p.products_status = 1 and p.products_quantity > 0 union all select p.products_id from products p join products_to_categories p2c on p.products_id = p2c.products_id where p2c.categories_id = 1 and p.products_status = 1 and p.products_sklad > 0) ta s použitím withWITH filtered_products (products_id, products_quantity, products_sklad) AS (select p.products_id, p.products_quantity, p.products_sklad from products p join products_to_categories p2c on p.products_id = p2c.products_id where p2c.categories_id = 1 and p.products_status = 1)select count(distinct products_id) as total from ( select p.products_id from filtered_products p where p.products_quantity > 0 union all select p.products_id from filtered_products p where p.products_sklad > 0) tTakhle možná jsi myslel to použití with, ale planner to vyhodnotí stejně.K doplnění, LEFT JOIN nedává smysl, filtruji podle kategorie, potřebuji, aby se mi našla, takže INNER JOIN je správně. Pořadí tabulek v tomhle případě nebude dělat rozdíl, hlavní je chytnout relaci products_id + categories_id přes indexu, takže zapsanému pořadí v dotazu by měl odpovídat složený index ve stejném pořadí sloupců.

Napsal TomášX;1645884

odpovíd ti podrobněji večer, v první řadě,  proč tam dáváš "p.product_sklad" místo "p.product_sklad > 0"? To generuje úplně rozdílný typ dotazu, ve složeném indexu by takový sloupec musel být na začátku a zároveň tím aktiviješ implicitní přetypování, což může generovat nestabilní výsledky. Je to vidět i na tom explainu, kdy nebyly použity správně indexy.

Můžeš ještě zkusit tu moji variantu, ale "s > 0" u těch sloupců product_sklad a product_quantity? Schválně si pak porovnej explain, že i takhle drobná změna má dopad na běhový plán.

Ok, ale nepřijde mi, že to má vliv na rychlost:

díky, zajímavé je, že tam máš jiný počet záznamů. Co máš za verzi mariadb? Můžeš sem ještě hodit explain extended? Vůbec mi nesedí volba indexů, na třetím řádku v sloupci "Extra" máš uvedeno "using where" to znamená, že se data pro řádek musely načíst do paměti a tam se muselo runtime porovovnat, to dělalo nejspíš pro podmínku na sklad a status, ta by ale měla být v indexu a není důvod, aby jí to nezvolilo.Moc víc nejspíš s tímhle dotazem bez přepsání podmínek nepohneme, ty dvě podmínky na products_sklad a products_quantity jsou totiž problém, buď tam hledáš inverzi (tj. cokoliv, co není "false") nebo range (tj. nenulový počet). Řešením může být jak jsem psal na začátku, zkus ty dva sloupce sjednotit do jednoho při zápisu.

Počty záznamů neřeš, tam se to mění, jak se produkty rozřazují.  Testuji to na mirroru live db. Ten WITH mi vůbec nefunguje, je možné, že by WITH byl vypnutý v konfiguraci? Verze: 10.1.48-MariaDB-0+deb9u2