Fórum Root.cz
Hlavní témata => Vývoj => Téma založeno: bmn 10. 04. 2022, 14:46:35
-
Mám dve tabuľky kde prvá obsahuje súbory a druhá atributy k týmto súborom.
CREATE TABLE files (id INTEGER PRIMARY KEY AUTOINCREMENT, file VARCHAR(255) UNIQUE, mtime DATETIME);
CREATE TABLE headers (id INTEGER PRIMARY KEY AUTOINCREMENT, id_file INTEGER,key VARCHAR(81), value VARCHAR(81), comment VARCHAR(81), FOREIGN KEY(id_file) REFERENCES fits_files(id) ON DELETE CASCADE);
Takže chcem vyhľdať files ktoré majú daný key=value atribút popripade aj kombináciu viacerych key=value. key=value je dynamické nie je presne určené ktorý súbor koľko atrobútov a tiež jeden súbor môže mať viacero atribútov s rovnakým key.
Napísal som tieto dva SQL dotazy ktoré robia to čo chcem. Čo je ale zaujímave tak prvý trvá 0.113 a druhý 0.231. Ten druhý bol pokus o optimalizáciu čo ale nevyšlo.
SELECT file,GROUP_CONCAT(CASE WHEN key="key1" THEN value END) AS "KEY1",GROUP_CONCAT(CASE WHEN key="KEY2" THEN value END) AS "KEY2" FROM fits_files LEFT JOIN files ON files.id=id_file GROUP BY files.id HAVING "KEY1" LIKE "Blue";
SELECT f.file,h1.value,h2.value FROM files AS f JOIN headers AS h1 ON f.id=h1.id_file AND h1.key="KEY1" AND h1.value="-15" JOIN headers AS h2 ON f.id=h2.id_file AND h2.key="KEY2" AND h2.value = "Blue";
Dá sa napísať nejaký optimálnejší SQL dotaz. Poprípade zvoliť nejaký úplne iný prístup? Súborov bude rádovo desaťtisíce a atribútov 10-20 krát toľko.
-
A keď hľadáš podľa key/value máš index na
headers(key, value)?
Ak chceš vidieť, čo presne tvoj dotaz robí, pozri sa na EXPLAIN QUERY PLAN a prípadne to tiež pošli sem, pretože tam presne uvidíš, ako DB tvoj dotaz vyhodnocuje a kde je problematické miesto (ideálne by si tam nechcel vidieť SCAN ale SEARCH).
-
Hej to som skusal.
QUERY PLAN
|--SCAN TABLE files
`--SEARCH TABLE headers USING AUTOMATIC COVERING INDEX (id_file=?)
QUERY PLAN
|--SEARCH TABLE headers AS h1 USING INDEX key (key=?)
|--SEARCH TABLE files AS f USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH TABLE headers AS h2 USING INDEX key (key=?)
Po pridanie index headers(key, value)
QUERY PLAN
|--SEARCH TABLE headers AS h1 USING INDEX key_value (key=? AND value=?)
|--SEARCH TABLE files AS f USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH TABLE headers AS h2 USING INDEX key_value (key=? AND value=?)
To padlo z 0.258 na 0.043 takze super diky za ten tip na index. Predtym som mal len zvlast dva index na key a value.
-
Není v tabulce headers sloupec ID zbytečný? Jeden soubor asi nemůže mít více stejných hodnot, pak bych PK volil (id_file, key).
-
Práveže môže mať viacero rovnakých key.