mMn fehlende indexe

[storchi]

Hi,

ich wusele mit dem kodi plugin (Version ) unter osmc auf meinem raspi 3b+ mit nur 1GB Ram. Leider ist die Benutzung extrem langsam, teilweise warte ich zB. 25s auf "Browse Shows -> All Channels". Die db passt definitiv nicht in den Ram Swapping hilft, sollte daher zum debuggen aus sein.

Ich hab mich per sqlite3 direkt mit der db verbunden: sqlite3 /home/osmc/.kodi/userdata/addon_data/plugin.video.mediathekview/filmliste-v3.db .timer on .eqp on

sql query: (ich glaube für Browse Shows -> All Channels) SELECT CASE WHEN UPPER(SUBSTR(showname,1,1)) between 'A' and 'Z' THEN UPPER(SUBSTR(showname,1,1)) WHEN SUBSTR(showname,1,1) between '0' and '9' THEN '0' ELSE '#' END, COUNT(DISTINCT(SHOWID)) FROM film where (1=1) GROUP BY CASE WHEN UPPER(SUBSTR(showname,1,1)) between 'A' and 'Z' THEN UPPER(SUBSTR(showname,1,1)) WHEN SUBSTR(showname,1,1) between '0' and '9' THEN '0' ELSE '#' END ORDER BY CASE WHEN UPPER(SUBSTR(showname,1,1)) between 'A' and 'Z' THEN UPPER(SUBSTR(showname,1,1)) WHEN SUBSTR(showname,1,1) between '0' and '9' THEN '0' ELSE '#' END asc; dauert 27s wegen dem: QUERY PLAN |--SCAN TABLE film `--USE TEMP B-TREE FOR GROUP BY also ein voller Durchlauf der gesamten Tabelle

nach: create index idx_2 on film (showid,showname); dauert der query 7s wegen dem: QUERY PLAN |--SCAN TABLE film USING COVERING INDEX idx_2 `--USE TEMP B-TREE FOR GROUP BY er nutzt also den index idx_2

noch krasser: (von def getChannels) SELECT channel AS channelid, channel, 0 as count FROM film GROUP BY channel ORDER BY channel ASC; von 23s auf 0.8s nach: CREATE INDEX idx_1 on film (channel);

Ich hab bestimmt noch weitere Indexe vergessen. Kann das mal jemand gegenchecken? Ich vermute, selbst für potenteren Endgeräte würde das was bringen.

Die Indexe müssten dann idealerweise schon beim tgl. "Update" dabei sein...

vg

[storchi]

Sendungen per Sender: SELECT showid, channel as channelId, showname, channel from film where (channel="ZDF") GROUP BY showid, channel, showname ORDER BY showname asc; von 18s auf 1.2s

[codingPF]

Ja auf meinem 3b+ sehe ich das auch. Die Filmliste ist in den letzten Jahren von 450k auf 700k gewachsen was leider für den RAM von RP nicht zutrifft. Das Problem mit den Indexen ist, dass sich je nach (Client) Settings und “Seite” die where-clause ändert und die Indexe nicht greifen. Auch ist der Index nach dem Anlegen schnell weil er noch im Cache ist - wenn du 2 Std wartest und dann die Abfrage machst, muss erstmal die Datei aus dem FS gelesen werden. Imho ist da das große Problem. Wenn du einen schönen Index findest, schau ich mir das gerne an aber vor 1-2 Jahren konnte ich nicht allgemein gültiges finden das auch bestand hat

[storchi]

hi, danke für deine Antwort. Bei dem ersten query siehst du, dass in der where Klausel nur 1=1 steht. Das kann man sowieso weglassen. Der eqp zeigt dir, dass der index idx_2 für das group by genutzt wird. Dass der Index nach gewisser zeit aus dem fs geladen werden muss ist klar. Dafür ist er aber viel kleiner und viel schneller geladen als ein kompletter Table scan und kann obendrein noch leichter im cache gehalten werden. Ich denke die zwei indexe von oben bringen schon sehr viel (für meinen usecase). Ich gehe davon aus, dass andere usecases weitere indexe erfordern. chatgpt hat mir verraten, dass man ersteren query auch so schreiben kann:

SELECT CASE WHEN UPPER(SUBSTR(showname, 1, 1)) BETWEEN 'A' AND 'Z' THEN UPPER(SUBSTR(showname, 1, 1)) WHEN SUBSTR(showname, 1, 1) BETWEEN '0' AND '9' THEN '0' ELSE '#' END AS initial_letter, COUNT(DISTINCT(SHOWID)) AS letter_count FROM film GROUP BY initial_letter ORDER BY initial_letter;

für die Lesbarkeit finde ich das sehr hilfreich.

vg

[codingPF]

Nicht schlecht von der kleinen sqlite. Das Feature unterstützen nicht alle DB. Muss ich mal schauen ab welcher Version das geht - ggf kann man da den Code entlasten. Für die Indexe muss man sich das anschauen - welche etwas bringen für welchen Preis (größer der DB etc…