Vyvíjej vlastní projekty a expanduj do zahraničí! Jak. Přijď na 13. Affiliate konferenci v Praze
Zobrazují se odpovědi 1 až 14 z 14

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

  1. 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:

    Kód:
    SELECT * FROM `articles`
    
    JOIN `article_options` ON `article_options`.`article_id` = `articles`.`id`
    JOIN `article_option_types` ON `article_options`.`type_id` = `article_option_types`.`id`
    
    WHERE `articles`.`removed` =0 
    AND `article_option_types`.`name` = "active" AND `article_options`.`value` = 1
    AND `article_option_types`.`name` = "active" AND `article_options`.`value` = 1 
    // výše je druhý parametr recommend a tady je problém, protože ani AND ani OR neudělá to správné.
    
    ORDER BY articles.id DESC LIMIT 5
    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.

  2. Co se právě děje na Webtrhu?
    Armin_van_Buuren poptává: XML feed - import zboží do Prestashopu
    Pugis poptává: Poptávám tvorbu webu (agregátor)
    Holub Michal poptává: Úprava Wordpress blogu
  3. Kód:
    select * from article_option_types
    inner join articles on articles.id = article_option_types.id
    where (article_option_types.name="active" or article_option_types.name="recommend") and articles.removed=0
    order by articles.id desc
    limit 5
    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.

  4. 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?

  5. 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í :-)

  6. Citace Původně odeslal smitka Zobrazit příspěvek
    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í.

  7. Citace Původně odeslal musil.david Zobrazit příspěvek
    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ý.

  8. Citace Původně odeslal brooks Zobrazit příspěvek
    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.

  9. 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.

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

    Kód:
    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`);

  11. Kód:
    SELECT DISTINCT a.id FROM articles AS a 
    	JOIN article_options AS o ON (o.article_id=a.id AND o.value = 1)
    	JOIN article_option_types AS t ON (o.type_id=t.id AND t.name IN ('active','recommend') AND t.removed != 1)
    WHERE a.removed != 1
    ORDER BY a.id DESC LIMIT 5
    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

  12. 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...

  13. jasne, nevsiml sem si ze to potrebujes pro oba naraz, neni to nesmyslne jen se to musi napsat trochu jinak
    Kód:
    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 != 1
    ORDER BY a.id DESC LIMIT 5

  14. Citace Původně odeslal Aleš Jiříček Zobrazit příspěvek
    jasne, nevsiml sem si ze to potrebujes pro oba naraz, neni to nesmyslne jen se to musi napsat trochu jinak
    Kód:
    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 != 1
    ORDER BY a.id DESC LIMIT 5
    Díky! Zatím to neběhá, ale pohraju si a to už snad rozchodím :)

  15. 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...

Hostujeme u Server powered by TELE3