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.