View Single Post
Old 06-24-2011, 09:26 AM   #32
yuri_b
Connoisseur
yuri_b will become famous soon enoughyuri_b will become famous soon enoughyuri_b will become famous soon enoughyuri_b will become famous soon enoughyuri_b will become famous soon enoughyuri_b will become famous soon enough
 
Posts: 71
Karma: 592
Join Date: Aug 2010
Device: irex dr800sg DR1000S
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 !!!
yuri_b is offline   Reply With Quote