Správný způsob algoritmizace SQL dotazů ve spojení s PHP

24. 7. 2018 08:36:55

Ahoj,

jak se postupem času dostávám k náročnějším projektům, uvědomuju si čím dál tím více, že je třeba pečlivě přemýšlet nad optimalizací SQL dotazů. Konkrétně ve spojení s PHP dotazy. Rád bych se zeptal na následující problematiku.

Uvažujme jednodušší příklad zpravodajského portálu, kdy máme tisíce článků. Pro lepší orientaci budou články pouze jednojazyčně, tak hlavní tabulka "articles" článku v DB ve formě: id, user_id, title, perex, content, created, removed - to pro logiku staci.

Ve druhé tabulce jsou definovány příznaky, které článek může mít., např.: active, top, recommend, apod. Forma tabulky "article_option_types": id, name

A třetí tabulka "article_options" toto sjednocuje: id, article_id, type_id, value

Jak by se pak správně mělo postupovat při výpisu článků, které chci vypsat třeba v sidebaru webu? Potřebuji vypsat pět článků a potřebuji, aby měly příznaky active a recommend.

Je lepší nejdříve projít tu spojovací tabulku a vytahat id článků, které pak vypíšu pomocí sql IN_ARRAY() a nebo je lepší to poskládat všechno do jednoho dotazu, kde by tedy musel být asi nějaký vnořený, který mi postupně projede ty příznaky?

Je třeba v dotazu obsáhnout i LIMIT a také ORDER BY.

Tohle je základ:

Ještě mě napadlo nahradit parametry jejich číselnými idčky, na to bych nepotřeboval už JOINovat tu druhou tabulku, kde jsou příznaky definovány.

Díky za tipy.

24. 7. 2018 08:36:55

https://webtrh.cz/diskuse/spravny-zpusob-algoritmizace-sql-dotazu-ve-spojeni-s-php#reply1357810

brooks

24. 7. 2018 09:00:40

5 nejnovějších nesmazaných článků, co mají active nebo recommend

EDIT: blbě koukám, v article_option_types jsou jen definice typů. Tady bch to zbytečně nekomplikoval, hodil bych article_option_types jen třeba article_id|name, tzn 26|top, 26|recommend.

24. 7. 2018 09:00:40

https://webtrh.cz/diskuse/spravny-zpusob-algoritmizace-sql-dotazu-ve-spojeni-s-php#reply1357809

David Musil

(69 hodnocení)

24. 7. 2018 09:38:07

Ten sql kod, co jsi poslal ale vybere active nebo recommend pokud se nepletu. Ja potrebuji oba platne najednou :)

Edit: aha, to asi resi ten inner, ze ano?

24. 7. 2018 09:38:07

https://webtrh.cz/diskuse/spravny-zpusob-algoritmizace-sql-dotazu-ve-spojeni-s-php#reply1357808

Vladimír Smitka

(4 hodnocení)

24. 7. 2018 10:49:36

Za by měla optimalizace databáze začít úpravnou indexů dle průzkumu pomocí EXPLAIN (https://php.vrana.cz/ukazka-pouziti-indexu.php, http://explain.cz/). To pomůže vybrat nejlepší dotaz pro daný SQL server (například starší MySQL neuměla indexy v subselectech).

Můžou z toho pak vyjít nápady, že by třeba nějaké věci bylo lepší řešit až na úrovni aplikace, nebo třeba dotaz rozložit do více kroků - například nejprve projet spojovací tabulku na active a recommend, groupnout ji a vytáhnout si jen ty ID příspěvků, které mají HAVING count() = 2 a podle tohoto seznamu si vybrat dané příspěvky. Jednoduchý deterministický dotaz s krátkým výsledkem server i mnohem raději zacachuje.

Obecně také není dobré u náročných dotazů používat * a pracovat jen s nutným počtem sloupců.

Další věc je, že zrovna tento typ dotazů by měl být ideálně cachovaný už na straně aplikace, dokud se něco nezmění (což aplikace ví) a pak se vůbec vykonávat nemusí :-)

24. 7. 2018 10:49:36

https://webtrh.cz/diskuse/spravny-zpusob-algoritmizace-sql-dotazu-ve-spojeni-s-php#reply1357807

David Musil

(69 hodnocení)

24. 7. 2018 11:02:26

Napsal smitka;1476086
Za by měla optimalizace databáze začít úpravnou indexů dle průzkumu pomocí EXPLAIN (https://php.vrana.cz/ukazka-pouziti-indexu.php, http://explain.cz/). To pomůže vybrat nejlepší dotaz pro daný SQL server (například starší MySQL neuměla indexy v subselectech).
Můžou z toho pak vyjít nápady, že by třeba nějaké věci bylo lepší řešit až na úrovni aplikace, nebo třeba dotaz rozložit do více kroků - například nejprve projet spojovací tabulku na active a recommend, groupnout ji a vytáhnout si jen ty ID příspěvků, které mají HAVING count() = 2 a podle tohoto seznamu si vybrat dané příspěvky. Jednoduchý deterministický dotaz s krátkým výsledkem server i mnohem raději zacachuje.
Obecně také není dobré u náročných dotazů používat * a pracovat jen s nutným počtem sloupců.
Další věc je, že zrovna tento typ dotazů by měl být ideálně cachovaný už na straně aplikace, dokud se něco nezmění (což aplikace ví) a pak se vůbec vykonávat nemusí :-)

Díky za info, prostuduju to.

Jen mě napadá, že když začnu tou spojovací tabulkou a chci třeba pět článků, tak tohle mi vyhodí Idčka všech, které té vazbě budou vyhovovat a můžou jich být tisíce. A nejsem takový databázový znalec, abych si řekl, zda je to zbytečné či není.

24. 7. 2018 11:02:26

https://webtrh.cz/diskuse/spravny-zpusob-algoritmizace-sql-dotazu-ve-spojeni-s-php#reply1357806

brooks

24. 7. 2018 11:29:49

Napsal musil.david;1476065
Ten sql kod, co jsi poslal ale vybere active nebo recommend pokud se nepletu. Ja potrebuji oba platne najednou :)
Edit: aha, to asi resi ten inner, ze ano?

Jo, blbě jsem koukal dvakrát :-)

Pokud potřebuješ podle obou, tak já bych vybral idčka článků z tabulky article_option_types podle pravidel - jedno pravidlo, jeden select a nacpat do pole. Prohnat to array_intersect(), potom implode() a následně do selectu na articles where id in()

Což bude rychlý a pokud se to abstrahuje, abys to nemusel psát furt dokola, tak i krásně znovupoužitělný.

24. 7. 2018 11:29:49

https://webtrh.cz/diskuse/spravny-zpusob-algoritmizace-sql-dotazu-ve-spojeni-s-php#reply1357805

David Musil

(69 hodnocení)

24. 7. 2018 11:31:28

Napsal brooks;1476099
Jo, blbě jsem koukal dvakrát :-)
Pokud potřebuješ podle obou, tak já bych vybral idčka článků z tabulky article_option_types podle pravidel - jedno pravidlo, jeden select a nacpat do pole. Prohnat to array_intersect(), potom implode a následně do selectu na articles where id in()
Což bude rychlý a pokud se to abstrahuje, abys to nemusel psát furt dokola, tak i znovupoužitělný.

Urcite takhle to mam v planu. Jen jsem vyjadril obavu nad tim, ze me zajima treba jen pet clanku, ale z tech pravidel dostanu treba tri tisice idcek clanku. Ale potrebuju jich pet.

24. 7. 2018 11:31:28

https://webtrh.cz/diskuse/spravny-zpusob-algoritmizace-sql-dotazu-ve-spojeni-s-php#reply1357804

David Musil

(69 hodnocení)

24. 7. 2018 15:46:24

A nebo by slo zmenit trosku pojeti tech parametru. Nebude reccomend a active zaroven, protoze kdyz je neco reccomend, musi to byt active. Takze spise davat clankum jen status_id a pak vytahnu jednoduchou shodu. Kdyz budou statusy v podobe: public, draft, top, archived, hidden, tak si myslim, ze by to melo stacit pro naprostou vetsinu pripadu, kdy potrebuji dat nejake priznaky.

24. 7. 2018 15:46:24

https://webtrh.cz/diskuse/spravny-zpusob-algoritmizace-sql-dotazu-ve-spojeni-s-php#reply1357803

David Musil

(69 hodnocení)

24. 7. 2018 15:55:35

Jeste jsem pripravil zdroj dat, kdyby se s tim chtel nekdo parat :)

CREATE TABLE `articles` (  `id` smallint(5) UNSIGNED NOT NULL,  `name` char(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,  `rank` smallint(5) UNSIGNED NOT NULL,  `created` datetime DEFAULT NULL,  `edited` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `cms_user_id` tinyint(3) UNSIGNED DEFAULT NULL,  `removed` tinyint(1) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;INSERT INTO `articles` (`id`, `name`, `rank`, `created`, `edited`, `cms_user_id`, `removed`) VALUES(1, 'First Article', 0, NULL, '2018-07-24 13:53:00', 1, 0),(2, 'Second Article', 0, NULL, '2018-07-24 13:53:04', 1, 0),(3, 'Third Article', 0, NULL, '2018-07-24 13:53:10', 1, 0),(4, 'Fourth Article', 0, NULL, '2018-07-24 13:53:16', 1, 0);CREATE TABLE `article_options` (  `id` smallint(5) UNSIGNED NOT NULL,  `article_id` smallint(5) UNSIGNED DEFAULT NULL,  `type_id` smallint(5) UNSIGNED DEFAULT NULL,  `value` tinyint(1) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;INSERT INTO `article_options` (`id`, `article_id`, `type_id`, `value`) VALUES(1, 1, 1, 1),(2, 2, 1, 1),(3, 1, 2, 0),(4, 2, 2, 1),(5, 3, 1, 1);CREATE TABLE `article_option_types` (  `id` smallint(5) UNSIGNED NOT NULL,  `name` char(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,  `removed` tinyint(1) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;INSERT INTO `article_option_types` (`id`, `name`, `removed`) VALUES(1, 'active', 0),(2, 'top', 0),(3, 'other1', 0),(4, 'other2', 0);ALTER TABLE `articles`  ADD PRIMARY KEY (`id`),  ADD KEY `cms_user_id` (`cms_user_id`);ALTER TABLE `article_options`  ADD PRIMARY KEY (`id`),  ADD KEY `article_id` (`article_id`),  ADD KEY `type_id` (`type_id`);ALTER TABLE `article_option_types`  ADD PRIMARY KEY (`id`);ALTER TABLE `articles`  MODIFY `id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;ALTER TABLE `article_options`  MODIFY `id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;ALTER TABLE `article_option_types`  MODIFY `id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;ALTER TABLE `articles`  ADD CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`cms_user_id`) REFERENCES `cms_users` (`id`);ALTER TABLE `article_options`  ADD CONSTRAINT `article_options_ibfk_1` FOREIGN KEY (`article_id`) REFERENCES `articles` (`id`),  ADD CONSTRAINT `article_options_ibfk_2` FOREIGN KEY (`type_id`) REFERENCES `article_option_types` (`id`);

24. 7. 2018 15:55:35

https://webtrh.cz/diskuse/spravny-zpusob-algoritmizace-sql-dotazu-ve-spojeni-s-php#reply1357802

(20 hodnocení)

24. 7. 2018 18:18:22

jen nastrel, ale INNER JOIN se da pouzit i pro filtrovani, coz pak cely dotaz dost zjednodusuje...

ALE :) nezkoumal jsem explain ani vysledek, ani nic, nevim ze to v tomhle pripade bude to nejlepsi, jen je to mozna cesta

24. 7. 2018 18:18:22

https://webtrh.cz/diskuse/spravny-zpusob-algoritmizace-sql-dotazu-ve-spojeni-s-php#reply1357801

David Musil

(69 hodnocení)

24. 7. 2018 18:38:07

Diky, Alesi. Ale ono tohle reseni taky nevybira shodne zaznamy, kdy je prirazeno active i recommend zaroven, ale diky IN to vybere kdyz splnuje tu podminku pouze jednoho parametru.

Tim si zacinam myslet, ze ten muj pozadavek je asi nesmyslny...

24. 7. 2018 18:38:07

https://webtrh.cz/diskuse/spravny-zpusob-algoritmizace-sql-dotazu-ve-spojeni-s-php#reply1357800

(20 hodnocení)

24. 7. 2018 18:43:21

jasne, nevsiml sem si ze to potrebujes pro oba naraz, neni to nesmyslne jen se to musi napsat trochu jinak

24. 7. 2018 18:43:21

https://webtrh.cz/diskuse/spravny-zpusob-algoritmizace-sql-dotazu-ve-spojeni-s-php#reply1357799

David Musil

(69 hodnocení)

24. 7. 2018 18:46:19

Napsal Aleš Jiříček;1476184
jasne, nevsiml sem si ze to potrebujes pro oba naraz, neni to nesmyslne jen se to musi napsat trochu jinak
SELECT DISTINCT a.id FROM articles AS a JOIN (SELECT count(*) as count, article_id FROM article_options AS o JOIN article_option_types AS t ON (o.type_id=t.id AND t.name IN ('active','recommend') AND t.removed != 1)GROUP BY article_id) AS t ON (t.article_id=a.id AND t.count = 2)WHERE a.removed != 1ORDER BY a.id DESC LIMIT 5

Díky! Zatím to neběhá, ale pohraju si a to už snad rozchodím :)

24. 7. 2018 18:46:19

https://webtrh.cz/diskuse/spravny-zpusob-algoritmizace-sql-dotazu-ve-spojeni-s-php#reply1357798

(20 hodnocení)

24. 7. 2018 18:47:19

neni nic co by sql nedokazalo :) vzdycky je nejaka cesta... Joinovani subdotazu sem si posledni dobou docela oblibil, ale musi se to pouzivat fakt s rozumem, na tisici polozkach dobry, ale na milionu uz to dokaze nekdy zatopit :)) ale tak kdyz se spravne cachuje, nebyva to nutne takovy problem...

24. 7. 2018 18:47:19

https://webtrh.cz/diskuse/spravny-zpusob-algoritmizace-sql-dotazu-ve-spojeni-s-php#reply1357797

Pro odpověď se přihlašte.

Přihlásit