I did some analyse for queries:
Actual query is:
SELECT filename, directory_path, is_directory, file_type, file_size, file_time_modified, title, author, thumb_data_small FROM file_metadata AS m LEFT JOIN thumbnails AS t ON m.file_id = t.file_id WHERE tag = 'book' ORDER BY sort_priority DESC, (CASE WHEN title IS NOT NULL THEN 0 ELSE 1 END), title COLLATE IREX_CASE_INSENSITIVE ASC, filename COLLATE IREX_CASE_INSENSITIVE ASC, directory_path COLLATE IREX_CASE_INSENSITIVE ASC;
result of SQL analise of this query is:
SEARCH TABLE file_metadata AS m USING AUTOMATIC COVERING INDEX (tag=?)
SEARCH TABLE thumbnails AS t USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
USE TEMP B-TREE FOR ORDER BY
What I say?
Two search was use not direct indexing and building temporary tree in memory or on disk.
Very bad.
BTW the same for indexed table
. Indexes that I added was wrong !!!