SQLite key-value atribúty

bmn

  • ***
  • 145
    • Zobrazit profil
    • E-mail
SQLite key-value atribúty
« kdy: 10. 04. 2022, 14:46:35 »
Mám dve tabuľky kde prvá obsahuje súbory a druhá atributy k týmto súborom.

Kód: [Vybrat]
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.
Kód: [Vybrat]
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.


Re:SQLite key-value atribúty
« Odpověď #1 kdy: 10. 04. 2022, 19:13:24 »
A keď hľadáš podľa key/value máš index na
Kód: [Vybrat]
headers(key, value)?

Ak chceš vidieť, čo presne tvoj dotaz robí, pozri sa na
Kód: [Vybrat]
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).

bmn

  • ***
  • 145
    • Zobrazit profil
    • E-mail
Re:SQLite key-value atribúty
« Odpověď #2 kdy: 10. 04. 2022, 20:08:28 »
Hej to som skusal.
Kód: [Vybrat]
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)
Kód: [Vybrat]
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.
« Poslední změna: 10. 04. 2022, 20:10:12 od bmn »

fos4

Re:SQLite key-value atribúty
« Odpověď #3 kdy: 11. 04. 2022, 10:00:43 »
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).

bmn

  • ***
  • 145
    • Zobrazit profil
    • E-mail
Re:SQLite key-value atribúty
« Odpověď #4 kdy: 11. 04. 2022, 10:20:58 »
Práveže môže mať viacero rovnakých key.