Databáze a SQL mě baví, nepopírám. Jsem postižen svým studiem učitelství Matematiky – Fyziky a databázařina je pro mě nejvíce aplikovaná matematika při mém programátorském povolání.
Pro zjišťování existence entity v tabulce je několik cest, fungují všechny.
SELECT COUNT("foo_id") FROM "foo" WHERE "bar1"=podm1 AND "bar2"=podm2SELECT EXISTS(SELECT FROM "foo" WHERE "bar1"=podm1 AND "bar2"=podm2)SELECT TRUE FROM "foo" WHERE "bar1"=podm1 AND "bar2"=podm2 LIMIT 1
Edit 8. 2. 2014: Oprava, protože zapracoval bordel několikrát upravovaných tabulek, s indexy sice vyladěnými, nicméně zabordelenými po hledání správné skladby, pomůže VACUUM/GFIX. Vše vyjasnil především Pavel Stěhule.
Nejefektivnější řešení se mi jevilo poslední. (Pro jiné db než PgSQL je potřeba typ Boolean nahradit něčím jiným, například číslem 1, typ Boolean není tak běžný.) Návratovou hodnotou z relace bude buď TRUE (v jiné db třeba ta jednička), nebo NULL. Líbí se mi že jde o krátký zápis a měla by fungovat stejně rychle, jako druhá verze s EXISTS.
COUNT() je na něco jiného, otrocky spočítá, projde celou tabulku. Při dostatečném množství dat to nebude nejrychlejší cesta. Bohužel tento způsob vidím nejčastěji; při hledání na fórech, v převzatém kódu, nebo ukázkách.
Klíčové slovo EXISTS je tou nejsprávnější variantou, jak mi vyjasnila diskuze zde. Nezdál se mi subSELECT, nebojím se jich, ale hodněkrát jsem zaznamenal zmínky ve smyslu „radši hledej rozumná spojení (JOIN), než vnořené SELECTy, aby jsi získal data efektivněji“.
Zápisek je určen pro mou sklerózu :-).
Komentáře
8 komentářů: „SQL: Jak nezjišťovat existenci záznamu v tabulce.“
Myslim ze reseni je stejne efektivni jako predesle dve reseni.
V pripade ze jde pouze o kontrolu zda hodnota existuje, coz v podobe vraceni true v pripade uspechu jde, tak bych doplnil jeste limit, aby se zpracovani zastavilo u prvniho nalezeneho zaznamu, protoze v opacnem pripade bude dotaz mit temer stejnou slozitost jako predesle dva.
SELECT 1 FROM "foo" WHERE `bar1`= 'podm1' AND `bar2`= 'podm2' LIMIT 1;
V pripade ze bych chtel vedet pocet vyskytu, tak:
SELECT COUNT(1) FROM "foo" WHERE `bar1`= 'podm1' AND `bar2`= 'podm2';
Vraceni TRUE hodnoty je v tomto pripade hodne relativni, a zalezi na dalsim zpracovani, pokud probiha v jinem jazyce, tak je sem stejne result predan a zrejme bude kontrolovan na hodnotu false, kterou vraci v pripade neuspechu.
Navic tusim ze v pripade pouziti SELECT TRUE vraci MySQL hodnotu 1 ve sloupci pojmenovanem TRUE, ale tim si nejsem uplne jisty.
Pokud je nad (bar1, bar2) UNIQUE CONSTRAINT, tak všechny varianty vyjdou výkonostně nastejno. Ale pokud není, tak rozhodně nejvhodnější varianta 2, protože v ostatních variantách musí databáze projít všechny záznamy, které odpovídají podmínce. Naopak u varianty 2 stačí najít první takový záznam a může skončit. U většího množství dat to může být zcela diametrální rozdíl.
PS. Mluvím o normálních databázích, nevím jak to má MySQL.
No proto tam byla ta závorka, pro uživatele nePostgreSQL :-), dělám s kdečím, takže vím, že Bool není běžně definován.
Teoreticky by to mohlo vyjít nastejno, prakticky mi vyšla poslední varinta asi na 1/10 času, zkusit osekmout limitem je dobrý nápad (v reálu mám bar1 s bar2 jako unique klíč, Db objekt má kolem 5 milionů entit).
je to zavysly na typu databaze a nemas pravdu.
Navic SELECT TRUE FROM „foo“ WHERE „bar1″=podm1 AND „bar2″=podm2
Ti logicky vrati vsechny zaznamy, ktere odpovidaji podmince WHERE a vyberou ti z nej TRUE. Aspon v normlanich typej jako MSSQL nebo oracle, mysql mozna dela neco jinyho, ale divil bych se.
Mrkni na net a tam zjistis, ze pro mysql je nejrychlejsi kombinace limit 1 a exists
Pro zjišťování existence se doporučuje (a i já doporučuji) používat SELECT EXISTS(SELECT). Zřetelně se tím dává najevo, že jde o existenci nějaké entity a nikoliv o samotnou hodnotu – navíc ve všech možných kombinací to bude nejlépe optimalizované a nejlépe vykonávané – na Postgresu.
Jinak, co se dívám na prováděcí plány, tak pokud se chytí správný index a není tam extrémně moc duplicit, tak více-méně všechny variace jsou stejně rychlé.
[Pavel Stěhule]: Ajaj, tak od Tebe to beru nejvíce, protože vím, že koukáš i do zdrojáků. (Čímž nehodlám tvrdit, že nevěřím nikomu jinému :-)).
V tom případě mám něco blbě a teď začínám tušit zdroj problémů. Vývojová verze databáze, občas překopané indexy, občas změna struktury tabulek. V pondělí prubnu export-import.
No a teď můžu zápisek leda smazat, protože je to blbost, nebo překopat. COUNT se mi líbí nejmíň, nekolikrát jsem narazil na to, že je pomalý.
Ten blog nemaž – jen pooprav 🙂 je to informace pro ostatní
Pro tenhle typ úloh je důležité, jestli se použíjí indexy nebo ne (pokud tam mají být). Sám COUNT coby agregační funkce (s filtrem) má relativně malý overhead (který se ale pozná spíš nad 10K hodnot). Ale pokud tam nemáš index, tak COUNT projede kompletní tabulku a EXISTS se zastaví na prvním výskytu, takže statisticky cca 1/2 tabulky. A navíc, to má jiný smysl – COUNT použiješ, když tě zajímá skutečně počet.
Jinak když máš pomalý dotaz, tak vždy je zásadní prováděcí plán. Když se kopírují data z db do db, tak a) může se zapomenout index, b) můžeš mít neaktuální statistiky, c) můžeš mít jinou velikost tabulek a také jinou konfiguraci databáze (v pg např. work_mem nebo effective_cache_size a další proměnné silně ovlivňují podobu plánu), d) můžeš mít třeba nafouklý tabulky nebo indexy – a je potřeba VACUUM (ve firebirdu sweep či jak se to jmenuje) nebo REINDEX. Těch důvodů proč něco může být pomalé je docela dost – a k tomu se mohou přidat hw problémy