I am quite pleased with some performance improvements I've seen in some queries (5 minutes to 5 seconds!)
There were several enhancements for the SQLite query optimizer implemented in the last years, which probably results in better execution plans for your queries. Also 5 minutes appear to be quite long, even if you have a big media library.
I tried to qualify the inner queries, but wasn't able to figure out how to fix this query.
You need to an alias to the "path" column of the sub-select query as due to dropping the metadb_handle pseudo column now the columns "path" and "subsong" are automatically added without any qualifier to the query of a query node, when the result is sent to a playlist.
So your query could look like this:
SELECT m.path, c.MB_per_min, c.[bits per sample], c.samplerate, c.channels
FROM medialibrary m
JOIN
(SELECT path subselect_path, [bits per sample], samplerate, channels,
MAX(ROUND(CAST(filesize * 60 AS float)/(length_seconds * 1024 * 1024), 3)) AS MB_per_min, COUNT(*) AS cnt
FROM medialibrary
WHERE [bits per sample] > 16 OR [samplerate] > 44100
GROUP BY path, [bits per sample], samplerate, channels
) c
ON m.path = c.subselect_path
AND m.[bits per sample] = c.[bits per sample]
AND m.samplerate = c.samplerate
AND m.channels = c.channels
WHERE
(c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels > 2 AND c.MB_per_min > 58.600)
OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels > 2 AND c.MB_per_min > 38.600)
OR (c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels = 2 AND c.MB_per_min > 26.489)
OR (c.[bits per sample] >= 32 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 22.100)
OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 14.900)
OR (c.[bits per sample] = 24 AND c.[samplerate] IS 44100 AND c.channels = 2 AND c.MB_per_min > 13.900)
ORDER BY m.path
However, as grouping by path could only be useful for multitrack files with different subsongs (and only in very rare cases), as for single track files the path is always already unique your query could also be written like this:
SELECT c.path, c.MB_per_min, c.[bits per sample], c.samplerate, c.channels
FROM (
SELECT path, subsong, [bits per sample], samplerate, channels,
ROUND(CAST(filesize * 60 AS float)/(length_seconds * 1024 * 1024), 3) AS MB_per_min
FROM medialibrary
WHERE [bits per sample] > 16 OR [samplerate] > 44100
) c
WHERE
(c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels > 2 AND c.MB_per_min > 58.600)
OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels > 2 AND c.MB_per_min > 38.600)
OR (c.[bits per sample] = 24 AND c.[samplerate] IS 96000 AND c.channels = 2 AND c.MB_per_min > 26.489)
OR (c.[bits per sample] >= 32 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 22.100)
OR (c.[bits per sample] = 24 AND c.[samplerate] IS 48000 AND c.channels = 2 AND c.MB_per_min > 14.900)
OR (c.[bits per sample] = 24 AND c.[samplerate] IS 44100 AND c.channels = 2 AND c.MB_per_min > 13.900)
ORDER BY c.path
As there is no need for the group-by, there is also no need for the join. Adding the subsong column to the sub-select is necessary as the medialibrary is now only referenced in the sub-select and as mentionend "path" and "subsong" will be added automatically.
Actually, also the colums of the main select could be replaced by just 1, if you use the query only for sending results to a playlist., as creating a tree structure with the currently used columns doesn't appear to be useful for me.
Topic: [fb2k v2] SQL Tree (foo_uie_sql_tree) (Read 145432 times)
previous topic - next topic