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

Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #180 kdy: 23. 10. 2019, 10:27:07 »
Je to krátkozraké. Co když bude potřebný víc než jeden štítek? A co indexování štítků pro lepší vyhledávání? Bude to stíhat, když budu pravidelně potřebovat seznam štítků?

Pro evidenci domácích CD bych však jeden sloupeček pro štítky klidně použil.
To myslíte vážně? Pokud bude povoleno víc štítků, potřebuju vazební tabulku – nijak to nesouvisí s tím, zda mám nebo nemám číselník štítků. Indexování štítků se udělá tak, že se na příslušném sloupečku dá normálně index.


Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #181 kdy: 23. 10. 2019, 10:28:45 »
Filipovi bylo potvrzeno to, že jeho řešení je funkční, jen bylo poznamenáno, že to lze řešit prozíravěji. Celý ten diskusní šprajc nechápu, připadá mi to jako "proč to dělat dobře a stejně jednoduše, když to jde i blbě a stejně jednoduše".
Přesně tak. Vy navrhujete „proč to dělat dobře a jednoduše, když to jde i blbě a složitě“. Celý ten diskusní šprajc je jenom o tom, že když vy to chcete dělat blbě a složitě, klidně to tak dělejte, ale neraďte to začátečníkům jako best practice.

Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #182 kdy: 23. 10. 2019, 10:35:29 »
Zase jen dokola opakujete vyvrácené. Pokud chcete nějakou reakci, přečtěte si mé předchozí komentáře, nedává smysl, abych je znovu opakoval.

Jediné nové, co jste napsal, je zase nesmysl:
při přejmenování štítku to bude zbytečně zamykat všechny záznamy se štítkem, místo toho, co by si to prostě vedle změnilo štítek
Přejmenování štítku je výjimečná operace. Vy ale budete muset složitě řešit přidávání štítku i mazání štítku, což jsou mnohem častější operace.

SELECT DISTINCT v takovém případě bude full table scan
O existenci indexů jste už slyšel?

Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #183 kdy: 23. 10. 2019, 10:45:31 »
O existenci indexů jste už slyšel?

Radost indexovat field, ve kterém je pole štítků.

Logik

  • *****
  • 993
    • Zobrazit profil
    • E-mail
Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #184 kdy: 23. 10. 2019, 13:35:53 »
Citace
Přejmenování štítku je výjimečná operace. Vy ale budete muset složitě řešit přidávání štítku i mazání štítku, což jsou mnohem častější operace.
Ty jsi nikdy pořádnou aplikaci nevyvíjel, nebo fakt nevím. Na takovéto základní úkony (např. přidávání entit s relací n:m) jsou samozřejmě v každém rozumnějším frameworku nástroje, takže implementace nebude složitější, než bez "štítkové tabulky", je to na pár řádek.

Citace
Citace
SELECT DISTINCT v takovém případě bude full table scan
O existenci indexů jste už slyšel?
Evidentně narozdíl od Tebe nejen slyšel, ale umím je i používat a vím, kdy se použijí a kdy ne :-)

Kód: [Vybrat]
=> create index test on translations(table_name);
CREATE INDEX
=> vacuum analyze translations;
VACUUM
=> EXPLAIN SELECT DISTINCT table_name FROM translations ;                                 
QUERY PLAN                                 
----------------------------------------------------------------------------
 HashAggregate  (cost=11158.59..11158.77 rows=18 width=11)
   Group Key: table_name
   ->  Seq Scan on translations  (cost=0.00..10231.67 rows=370767 width=11)
(3 rows)

Pokud bys chtěl použít index, tak na to musíš v takovémto případě použít speciální "hack" pomocí CTE. Aneb jak jsou tvoje "jednoduchá" řešení "jednoduchá":
https://stackoverflow.com/questions/5973850/can-i-optimize-a-select-distinct-x-from-hugetable-query-by-creating-an-index-on


Kit

  • *****
  • 651
    • Zobrazit profil
    • E-mail
Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #185 kdy: 23. 10. 2019, 14:03:48 »
Důvod je prostý: Pokud má engine volbu, zda projít všechny záznamy nebo všechny klíče v indexu, zvolí průchod všemi záznamy. Distinct na to nemá vliv.

Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #186 kdy: 23. 10. 2019, 17:53:00 »
Evidentně narozdíl od Tebe nejen slyšel, ale umím je i používat a vím, kdy se použijí a kdy ne :-)
Umíte je používat? Vážně?

Kód: [Vybrat]
CREATE TABLE stitky (stitek VARCHAR NOT NULL);
CREATE INDEX idx_stitky ON stitky USING BTREE (stitek);
INSERT INTO stitky (stitek) VALUES ('Praha1'), ('Praha1'), ('Brno1'), ('Ostrava1'), ('Brno1'), ('Praha1'), ('Praha1'), ('Ostrava1'), ('Zlín1');

INSERT INTO stitky (stitek) VALUES ('Praha10'), ('Praha10'), ('Brno10'), ('Ostrava10'), ('Brno10'), ('Praha10'), ('Praha10'), ('Ostrava10'), ('Zlín10');
VACUUM;
EXPLAIN SELECT DISTINCT stitek FROM stitky ORDER BY stitek LIMIT 10;
QUERY PLAN                             
-------------------------------------------------------------------------------------------
 Limit  (cost=0.14..2.35 rows=10 width=7)
   ->  Unique  (cost=0.14..9.88 rows=44 width=7)
         ->  Index Only Scan using idx_stitky on stitky  (cost=0.14..9.63 rows=99 width=7)

Samozřejmě, pokud na uživatele vylejete stovky štítků, ať si v tom hledá sám, protože to aplikace neumí, bude to pro databázi jiný úkol. Nicméně i tam se jí index bude hodit alespoň pro řazení. Teda pokud ty štítky aspoň seřadíte.

Logik

  • *****
  • 993
    • Zobrazit profil
    • E-mail
Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #187 kdy: 23. 10. 2019, 18:52:26 »
Koukám, že jsi furt nepochopil, kde je v Tvé DB struktuře problém. Příklad byl ZÁMĚRNĚ bez řazení, aby Ti demonstroval, že INDEX NEUMÍ AKCELEROVAT OPERACI DISTINCT. V Tvém dotazu se index použije pouze na řazení, ale práci oproti full table scanu (nebo přesněji sekvenčnímu scanu, protože po nalezení dostatečného počtu hodnot se zastaví) Ti neušetří ani náhodou.

Problém je v tom, že v indexu jsou odkazy na VŠECHNY záznamy. NENÍ to seznam unikátních hodnot. Tedy když máš v tabulce milion záznamů, tak tam máš milion odkazů do primárního souboru. A pokud ten milion záznamů bude mít sto štítků s +- rovnoměrným rozdělením, tak Tvůj dotaz sice použije index, aby si ušetřil řazení, ale pořád ten Tvůj dotaz bude pro DB znamenat projít prvních sto tisíc záznamů indexu, aby těch deset unikátních hodnot našel. Skvělé, že?

Zkus si navýšit počet záznamů, aniž bys navýšil počet různých štítků - čas na dotaz Ti naroste. Btw. to je vidět i na Tvém vygenerovaném plánu, proč je tam asi ten operátor unique, že?
PS: A ano, mimochodem, přesně jsi to vystihl s tím NoSQL: ano přesně toto je důvodem boomu NoSQL databází. Protože když SQL databázi používají lidé, co jí nerozumí a dělají takovéto zprasené DB struktury, tak přechodem na NoSQL databázi jen získají. A typické je, že si myslí, že je to vina těch SQL databází....

Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #188 kdy: 23. 10. 2019, 20:46:51 »
Koukám, že jsi furt nepochopil, kde je v Tvé DB struktuře problém. Příklad byl ZÁMĚRNĚ bez řazení, aby Ti demonstroval, že INDEX NEUMÍ AKCELEROVAT OPERACI DISTINCT. V Tvém dotazu se index použije pouze na řazení, ale práci oproti full table scanu (nebo přesněji sekvenčnímu scanu, protože po nalezení dostatečného počtu hodnot se zastaví) Ti neušetří ani náhodou.
Příště se zase ohánějte tím, že mé příklady neodpovídají realitě. V mé DB struktuře je problém, že je připravená na reálné použití a není optimalizovaná na nesmyslné dotazy typu „potřebuji všechny štítky z databáze v náhodném pořadí“. Prozradím vám, kdy je ten váš dotaz obvykle potřeba – pokud je vaše aplikace tak neschopná, že si z ní uživatel raději data vyexportuje a pracuje s nimi dál v Excelu.

Problém je v tom, že v indexu jsou odkazy na VŠECHNY záznamy. NENÍ to seznam unikátních hodnot.
B-Tree vskutku není seznam, B-Tree je strom. Listy toho stromu jsou zaindexované hodnoty. Teprve od té hodnoty vede odkaz na seznam záznamů, kde se ta hodnota vyskytuje. Jenže v mnou uvedeném příkladu databáze ty záznamy vůbec nepotřebuje, protože hodnoty zná už z indexu.

bude pro DB znamenat projít prvních sto tisíc záznamů indexu
Mohl byste nám nastínit, jak podle vás vypadá takový databázový index? A jak ho databáze používá pro hledání, když podle vás prochází jakési „záznamy indexu“ jeden po druhém?

Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #189 kdy: 23. 10. 2019, 21:22:29 »
Listy toho stromu jsou zaindexované hodnoty. Teprve od té hodnoty vede odkaz na seznam záznamů, kde se ta hodnota vyskytuje.
Díval jsem se ještě na B-Tree indexy Oraclu a PostgreSQL, a tam tohle neplatí. PostgreSQL má u neunikátního B-Tree indexu zapsané v indexu hodnoty opakovaně, protože každý list obsahuje odkaz na jeden řádek. Oracle má u neunikátního B-Tree indexu ROWID jako součást klíče, což je prakticky to samé. Teoreticky by nad těmi indexy mohly mít implementovanou funkci „najdi další klíč“, ale asi by nebyla moc využívaná. Pokud byste tedy opravdu použil pro štítky B-Tree index, máte pravdu.

Nicméně pro rozumnou implementaci štítků je potřeba použít fulltextový index. Tam už je to s jejich strukturou rozmanitější, nicméně s tím, že pod jedním klíčem bude větší množství záznamů, obvykle počítají.

Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #190 kdy: 23. 10. 2019, 21:24:18 »
B-Tree vskutku není seznam, B-Tree je strom. Listy toho stromu jsou zaindexované hodnoty. Teprve od té hodnoty vede odkaz na seznam záznamů, kde se ta hodnota vyskytuje.

Ne takhle to nefunguje. B-Tree jako takové nepodporuje duplicitní záznamy. Prakticky se to omezení obchází tak, že se k indexu, který povoluje duplicitní hodnoty, interně dolepí "virtuální" sloupec, ve kterém je autoinkrementovaná hodnota. Tím se ze všech duplicitních klíčů stanou neduplicitní.

A to je ten problém, který Ti nedochází a kvůli kterému je Tebou navržená struktura zoufale neefektivní. Nelze totiž levně přejít na další záznam, který se liší nevirtuálním sloupcem. Musíš totiž buď udělat sekvenční scan, nebo pro nalezení dalšího unikátního štítku udělat nové hledání se složitostí O(log N), kde N je počet všech neunikátních štítků. S extra tabulkou pro štítky je to O(1).

Dostuduj si základy databází a normalizace dat, máš v tom dost velké mezery.

Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #191 kdy: 23. 10. 2019, 21:54:54 »
A to je ten problém, který Ti nedochází a kvůli kterému je Tebou navržená struktura zoufale neefektivní. Nelze totiž levně přejít na další záznam, který se liší nevirtuálním sloupcem.
Přečtěte si předchozí komentář, tam je to vysvětlené. Problém není ve struktuře dat, ale v použitém typu indexu. Přičemž pro štítky je B-Tree index nevhodný tak jako tak, pro štítky je potřeba používat fulltextový index.

Logik

  • *****
  • 993
    • Zobrazit profil
    • E-mail
Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #192 kdy: 23. 10. 2019, 22:26:08 »
Citace
Pokud byste tedy opravdu použil pro štítky B-Tree index, máte pravdu.
Já narozdíl od Tebe netvrdím, že existuje nějaký index, který by Ti v tomto případě pomohl. Tedy jsem Ti na tom nejpřirozenějším indexu ukázal, že je to nesmysl. Ty jsi tvrdil, že Ti index pomůže a použil jsi B-Tree také. Ale furt je myšlenka, že B-Tree bude v běžných DB akcelerovat DISTINCT menší omyl (jen evidentní nedostatek praxe), než snaha použít na tento účel fulltext (což je úplné nepochopení, k čemu tento typ indexů slouží).

Citace
Nicméně pro rozumnou implementaci štítků je potřeba použít fulltextový index.
A jakej index např. v Postgres tedy použiješ? GIN nebo GIST? Nebo jiný? A to budeš hodnoty ukládat jako typ TSQuery, nebo jako jaký typ? No potěš koště....A jak Ti prosímtě takovej index pomůže????? Dáš nám sem nějaký usecase?

Jen si ve snaze nemuset uznat omyl jsi jen vymyslel ještě větší koninu. Fulltext slouží k indexaci slov v textu, nikoli k indexaci "celých hodnot" ve sloupci.Ještě tak by byl usecase pro fulltext narvat všechny štítky do jednoho pole a nad ním udělat fulltext - tam by dával tento typ indexu smysl. Ale to by jednak byla jiná struktura, než kterou si doteď razil, jednak by to byl jednak naprosto ukázkový příklad nesmyslné denormalizace, jednak ani tak by db neuměla Ti vrátit seznam použitých štítků.

Citace
...a není optimalizovaná na nesmyslné dotazy typu „potřebuji všechny štítky z databáze v náhodném pořadí“.
To byla demonstrace, že v Tvojí  struktuře to ta databáze prostě efektivně dělat nebude. Jestli Ti to nedošlo, tak kdyby to db uměla efektivně vrátit seřazené, tak by to uměla vrátit efektivně i bez požadavku na řazení - nikde není zakázáno, aby to vrátila seřazené, i když ji o to nikdo neprosí.

Citace
....že je připravená na reálné použití....
Tak už se shodneme, že Tebou navržená struktura neumí ani tak základní věc, jako vylistovat použité štítky v čase O(N), kde N je počet unikátních štítků, nebo tedy máš ještě nějaký pokus, jak znásilnit relační databázi?


Kit

  • *****
  • 651
    • Zobrazit profil
    • E-mail
Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #193 kdy: 23. 10. 2019, 22:43:37 »
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.

Logik

  • *****
  • 993
    • Zobrazit profil
    • E-mail
Re:MySQL - podmíněný SELECT přes dvě tabulky
« Odpověď #194 kdy: 23. 10. 2019, 22:53:45 »
Když to je těžký, když někdo chce znova vymejšlet kolo a ještě ho vymyslí hranatý.... :-)