MySQL - podmíněný SELECT přes dvě tabulky

Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #195 kdy: 23. 10. 2019, 22:54:33 »
Já narozdíl od Tebe netvrdím, že existuje nějaký index, který by Ti v tomto případě pomohl.
Aha. Takže uchovávat množinu krátkých textů je podle vás pro počítače neřešitelný úkol. Viděl jste někdy spellchecker, úplně tu nejprimitivnější implementaci? To je prostě množina slov, ve které se dá rychle vyhledat, jestli v ní dané slovo je nebo není. Seznam těch slov je možné i vypsat. Té struktuře, která umožňuje rychlé vyhledávání, se říká – index.

To, co potřebujete pro práci se štítky, je našeptávač. Přičemž našeptávače se běžně používají, fungují, a jsou implementované právě pomocí fulltextových indexů. Vypsat všechny hodnoty našeptávač (resp. použitý index) samozřejmě také umí, akorát se taková funkce v našeptávači neposkytuje uživatelům, protože je k ničemu.

To je právě ten rozdíl v našem přístupu. Já považuju za samozřejmost pro práci se štítky fulltextové a podobnostní vyhledávání (našeptávač). Pro vás je to zbytečnost, pak se ale divíte, že máte v databázi velmi podobné štítky. Jediné, co dokážete uživateli nabídnout, je vypsat mu všechny štítky, ať s nimi může pracovat v nějakém normálním programu a místo vaší aplikace.

Ale aspoň z toho máme další hezkou poučku pro začátečníky: pokud vaši uživatelé chtějí vypisovat stovky záznamů (ať už najednou, nebo stránkovaně), znamená to, že jim poskytujete špatné funkce pro filtrování a vyhledávání. Nikdo nechce hledat něco očima ve stovkách záznamů (nebo si to exportovat do Excelu a tam s tím čarovat).


Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #196 kdy: 23. 10. 2019, 23:03:42 »
Budem se tu ještě dlouho plácat kolem štítků, když víme, že je to M:N? Máme na to jasný a osvědčený vzor s bázovou tabulkou, jedním číselníkem a jednou vazební tabulkou.
Snad byste nechtěl porovnávat databázový výkon pro běžné operace – tj. získání seznamu štítků daného objektu, získání objektů s daným štítkem a přidání a smazání štítku u daného objektu. To ne, tady se přece porovnává výkon tak zásadních operací, jako je výpis všech štítků.

Kit

  • *****
  • 704
    • Zobrazit profil
    • E-mail
Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #197 kdy: 23. 10. 2019, 23:18:47 »
Já narozdíl od Tebe netvrdím, že existuje nějaký index, který by Ti v tomto případě pomohl.
Aha. Takže uchovávat množinu krátkých textů je podle vás pro počítače neřešitelný úkol. Viděl jste někdy spellchecker, úplně tu nejprimitivnější implementaci? To je prostě množina slov, ve které se dá rychle vyhledat, jestli v ní dané slovo je nebo není. Seznam těch slov je možné i vypsat. Té struktuře, která umožňuje rychlé vyhledávání, se říká – index.

Je to řešitelné i bez M:N, ale používá se na to jiná metodika než prostý index. Udělá se další indexovaná tabulka, třeba i bez vazby na základní tabulku, ale zato s unikátním indexem, který nedovolí duplicitní vložení štítku. Taková tabulka už nemá milióny záznamů ale jen stovky a je tak pro našeptávač bezproblémově použitelná.

Je to denormalizované řešení, které tedy vyžaduje použití triggerů. Jeho možnou výhodou je, že se štítky po výmazu hlavního záznamu neztratí.

Kit

  • *****
  • 704
    • Zobrazit profil
    • E-mail
Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #198 kdy: 23. 10. 2019, 23:26:13 »
Budem se tu ještě dlouho plácat kolem štítků, když víme, že je to M:N? Máme na to jasný a osvědčený vzor s bázovou tabulkou, jedním číselníkem a jednou vazební tabulkou.
Snad byste nechtěl porovnávat databázový výkon pro běžné operace – tj. získání seznamu štítků daného objektu, získání objektů s daným štítkem a přidání a smazání štítku u daného objektu. To ne, tady se přece porovnává výkon tak zásadních operací, jako je výpis všech štítků.

Pokud se to udělá M:N, tak číselník štítků bude mít jen stovky záznamů a práce s nimi bude velmi rychlá, včetně našeptávání. Rozhodně rychlejší, než index štítků v dlouhé tabulce.

Logik

  • *****
  • 1 022
    • Zobrazit profil
    • E-mail
Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #199 kdy: 23. 10. 2019, 23:42:15 »
Citace
Viděl jste někdy spellchecker, úplně tu nejprimitivnější implementaci?
Viděl. Dokonce evidentně narozdíl od Tebe jsem custom-made fulltext i implementoval, takže vím, že
a) fulltext index se spelcheckerem vlastně nijak nesouvisí, jsou to dvě úplně jiné technologie. To, že některé DB mají ve fultextových indexech zabudovaný jako preprocessing možnost stemmingu, aby ho aplikovali na data před tím, než data uloží do fulltext indexu, to nic nemění - do fulltextových indexů jdou ukládat (a i se ukládají) i nenastemmovaná data a naopak jde stemovat i něco, co neuložím do indexu.

b) spellchecker/stemmer samotný používá zpravidla hunspell algoritmus, kde sice má databázi slov, ale ve velmi speciálním tvaru (pomocí seznamu kořenů, prefixů a infixů) a jde o zcela jiné uložení dat, než je jak v B-Tree, tak ve fulltextových indexech - a s otázkou, jak něco ukládat a vyhledávat v relační databázi vůbec nesouvisí.

c) pro našeptávač je v tomto případě asi nejlepší varianta použít tzn. trigram indexy, které narozdíl od stemmingu ve fulltext indexech umí vyhledat i překlepy, to ovšem je úplně jiný typ indexu, než fulltextový index - a stejně jako fulltextový index nejde použít k akceleraci operace distinct.
d) stemming může pro jedno slovo vrátit více kořenů, takže zmiňovat ho v souvislosti s akcelerací DISTINCT operace je nesmysl.

V každém případě vyhledávání štítků pro účely našeptávání je zcela jiná úloha, než o které jsme se bavili. Kdo se tady furt vztekal, že nedodržujeme zadání? :-). My jsme se bavili o tom, jak udělat admin štítků, kde kupodivu seznam štítků potřebuji vidět i celý, např. pro to, aby ho mohl člověk jednou za čas zrevidovat a např. sloučit duplicity. Ono v reálu - pokud na to ten nástroj je - tak jde udržovat seznam štítků rozumně dlouhý a tedy revidovatelný. U této úlohy začala debata o indexu a na začátku to byl pro Vás snadný úkol, kde jste se mne snažil poučovat o indexech. Teď jsme se najednou dobrali do situace, kdy se zjistilo, že to opravdu neumíte rozumným způsobem udělat a tak to najednou není potřeba....
« Poslední změna: 23. 10. 2019, 23:44:13 od Logik »


Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #200 kdy: 24. 10. 2019, 07:08:43 »
V každém případě vyhledávání štítků pro účely našeptávání je zcela jiná úloha

Našeptávání je úplně jiná úloha. Když odmyslíme překlepy, tak našeptávání urychluje dohledání slova pomocí substringu (v nejjednodušším případě zleva). Na našeptání slova "hovado" je provedeno až šest dotazů a cílem je urychlit je jako celek. Naprosto nelze srovnat s dohledáním slova jedním dotazem, natož provedení distinkce.

Filipe, na DISTINCT opravdu indexy nezabírají. V mateřské škole pro databázové pracovníky se polopaticky učí, že je nutné data co nejvíc omezit a vyfiltrovat před tím, než se provádí distinct. Typickým zabijákem výkonu jsou distinct subselecty, distinct joiny apod., právě z tohoto důvodu.

Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #201 kdy: 24. 10. 2019, 21:12:11 »
Našeptávání je úplně jiná úloha.
Našeptávání je jediná reálná úloha, kdy uživatel pracuje se seznamem štítků. Jak už jsem psal, pokud píšete databázovou aplikaci, kde uživatel často potřebuje získat seznam stovek nebo více záznamů, aby v nich pak sám něco ručně hledal, je na vaší aplikaci něco hodně špatně. Takže úloha „dej mi seznam všech štítků“ je sice hezké teoretické cvičení, ale pro praxi je to k ničemu.

Filipe, na DISTINCT opravdu indexy nezabírají.
Když píšete o implementaci nějakého konkrétního databázového enginu, konkrétně ho pojmenujte. Když to napíšete takhle obecně, někdo by si mohl myslet, že je to nějaký principiální problém.

spellchecker/stemmer
Až na to, že spellchecker je něco úplně jiného, než stemmer. Spellchecker je prostý dotaz „existuje toto slovo?“, případně vylepšený o „pokud neexistuje, jaká podobná slova existují?“. Stemmer je nalezení kořene slova.

Rozhodně rychlejší, než index štítků v dlouhé tabulce.
Jenom jestli to ví třeba Google, že vyhledávání slova ve fulltextovém indexu závisí na celkovém počtu dokumentů a ne na počtu různých slov v indexu.

Kit

  • *****
  • 704
    • Zobrazit profil
    • E-mail
Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #202 kdy: 24. 10. 2019, 21:47:23 »
Filipe, na DISTINCT opravdu indexy nezabírají.
Když píšete o implementaci nějakého konkrétního databázového enginu, konkrétně ho pojmenujte. Když to napíšete takhle obecně, někdo by si mohl myslet, že je to nějaký principiální problém.

Jenže on to je principiální problém, se kterým si B-stromy ani heše neporadí. To by musel být ke každému klíči přidělen seznam ID záznamů, které tomu klíči vyhovují, abys mohl vždy vybrat jen ten první a jít dál.

Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #203 kdy: 24. 10. 2019, 22:04:03 »
Jenže on to je principiální problém, se kterým si B-stromy ani heše neporadí. To by musel být ke každému klíči přidělen seznam ID záznamů, které tomu klíči vyhovují, abys mohl vždy vybrat jen ten první a jít dál.
Existují ale i jiné druhy indexů. Hash si s tím neporadí, protože v indexu není uložena hodnota. B-Tree si s tím může poradit například právě tím způsobem, jaký jste popsal – v každém listu bude uložen seznam záznamů, které ten klíč obsahují. Vybírat první záznam není potřeba, protože hodnotu zná databáze už z indexu. Problém s B-Tree je jenom tehdy, pokud se používá upravená varianta, která umožňuje ukládat duplicitní klíče přímo jako samostatné uzly stromu – jako to má právě PostgreSQL nebo Oracle. To ale není obecná vlastnost B-Tree, naopak je to spíš odchylka těchto databází od standardní implementace B-Tree (i když je to odchylka logická, protože umožňuje řešit neunikátní hodnoty pořád v rámci jedné datové struktury B-Tree).

Kit

  • *****
  • 704
    • Zobrazit profil
    • E-mail
Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #204 kdy: 24. 10. 2019, 22:20:51 »
Jenže on to je principiální problém, se kterým si B-stromy ani heše neporadí. To by musel být ke každému klíči přidělen seznam ID záznamů, které tomu klíči vyhovují, abys mohl vždy vybrat jen ten první a jít dál.
Existují ale i jiné druhy indexů. Hash si s tím neporadí, protože v indexu není uložena hodnota. B-Tree si s tím může poradit například právě tím způsobem, jaký jste popsal – v každém listu bude uložen seznam záznamů, které ten klíč obsahují. Vybírat první záznam není potřeba, protože hodnotu zná databáze už z indexu. Problém s B-Tree je jenom tehdy, pokud se používá upravená varianta, která umožňuje ukládat duplicitní klíče přímo jako samostatné uzly stromu – jako to má právě PostgreSQL nebo Oracle. To ale není obecná vlastnost B-Tree, naopak je to spíš odchylka těchto databází od standardní implementace B-Tree (i když je to odchylka logická, protože umožňuje řešit neunikátní hodnoty pořád v rámci jedné datové struktury B-Tree).

To by nebyl B-Tree.

V hešovaném indexu se neukládá heš, ale přímo klíče.

Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #205 kdy: 24. 10. 2019, 22:33:48 »
Problém s B-Tree je jenom tehdy, pokud se používá upravená varianta, která umožňuje ukládat duplicitní klíče přímo jako samostatné uzly stromu – jako to má právě PostgreSQL nebo Oracle. To ale není obecná vlastnost B-Tree, naopak je to spíš odchylka těchto databází od standardní implementace B-Tree (i když je to odchylka logická, protože umožňuje řešit neunikátní hodnoty pořád v rámci jedné datové struktury B-Tree).

Aha, tak teď už rozumím, proč jste posílal explain z Postgresu. Chtěl jste demonstrovat, jak to Postgres ani nemůže umět!

Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #206 kdy: 24. 10. 2019, 22:51:09 »
To by nebyl B-Tree.
Pokud něco z uváděného není B-Tree, pak ta varianta používaná PostgreSQL,která umožňuje ukládat duplicitní klíče – protože striktní definice B-Tree vyžaduje lineárně uspořádané klíče.

V hešovaném indexu se neukládá heš, ale přímo klíče.
Hlavně tam musí být uložené odkazy na příslušné záznamy, které hashi odpovídají. Jestli tam budou uložené i klíče je věc implementace. Zrovna PostgreSQL nemá v hash indexu uložené klíče, ale jen jejich hashe. Zda záznam opravdu obsahuje požadovanou hodnotu (nebo jde jen o kolizi hashů) se zjistí teprve porovnáním skutečného záznamu z tabulky. Hash indexy v PostgreSQL tedy není možné použít pro optimalizované načítání dat přímo z indexu (bez čtení samotné tabulky).

Kit

  • *****
  • 704
    • Zobrazit profil
    • E-mail
Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #207 kdy: 24. 10. 2019, 23:00:16 »
To by nebyl B-Tree.
Pokud něco z uváděného není B-Tree, pak ta varianta používaná PostgreSQL,která umožňuje ukládat duplicitní klíče – protože striktní definice B-Tree vyžaduje lineárně uspořádané klíče.

Už výše bylo řečeno, že součástí toho klíče je i interní ID záznamu, které nemůže být duplicitní.

Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #208 kdy: 25. 10. 2019, 07:41:37 »
Už výše bylo řečeno, že součástí toho klíče je i interní ID záznamu, které nemůže být duplicitní.
Podle tohoto popisu není v PostgreSQL ID záznamu součástí klíče, ale je ve stromu uložené jako hodnota, na kterou se ten klíč odkazuje. Z čeho usuzujete na opak?

Kit

  • *****
  • 704
    • Zobrazit profil
    • E-mail
Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #209 kdy: 25. 10. 2019, 09:13:40 »
Už výše bylo řečeno, že součástí toho klíče je i interní ID záznamu, které nemůže být duplicitní.
Podle tohoto popisu není v PostgreSQL ID záznamu součástí klíče, ale je ve stromu uložené jako hodnota, na kterou se ten klíč odkazuje. Z čeho usuzujete na opak?

Takže to má jinak a připouští duplicitu klíče, viz klíč '49'. Jak by z tohoto měl jít udělat distinct jinak, než poctivým traverzováním?