joining on a ordered subquery: LEFT JOIN (SELECT * from `files` ORDER BY `files`.basename ASC) as `files` ON (`images_files`.`file_id` = `files`.`id`).Edit: it adds USING COVERING INDEX sqlite_autoindex_images_files_1 to the scan table clause, but does not appear to improve performance. In any case, it doesn't change the query plan. This isn't really feasible for my use case, as there may be rows in the images table without a corresponding file. changing the left joins to inner joins and removing the DISTINCT.|-SCAN TABLE images_files USING COVERING INDEX sqlite_autoindex_images_files_1 INNER JOIN `folders` ON (`files`.`parent_folder_id` = `folders`.`id`) INNER JOIN `files` ON (`images_files`.`file_id` = `files`.`id`) I tried eliminating images from the query altogether (not really feasible for my use case): EXPLAIN QUERY PLAN SELECT `images_files`.`image_id` This query plan looks similar to what I would expect from the original query - using the covering index to perform the sorting. `-SEARCH TABLE files USING COVERING INDEX index_files_on_parent_folder_id_basename (parent_folder_id=?) |-SCAN TABLE folders USING COVERING INDEX index_folders_on_path_unique Other query plans: EXPLAIN QUERY PLAN SELECT `files`.`id`Ĭhanging this to INNER JOIN gives the following query plan: QUERY PLAN |-SEARCH TABLE folders USING INTEGER PRIMARY KEY (rowid=?) |-SEARCH TABLE images_files USING COVERING INDEX sqlite_autoindex_images_files_1 (image_id=?) This results in the following query plan: QUERY PLAN ORDER BY `folders`.path, `files`.basename ASC LIMIT 40 OFFSET 0 LEFT JOIN `folders` ON (`files`.`parent_folder_id` = `folders`.`id`) Why isn't the path indexed used to order these results? How can I improve performance when ordering on these joined tables?Įdit: this is the query for sorting by folder path then basename: SELECT `images`.`id` My end goal is to order by folder path then basename, but I can't even optimise the basename scenario. Please note also that there may be where filtering depending on user query inputs. The images table is potentially large (I'm using 4M rows in my test database) and this query can take a very long time to return. |-SEARCH TABLE files USING INTEGER PRIMARY KEY (rowid=?) |-SEARCH TABLE images_files USING COVERING INDEX sqlite_autoindex_images_files_1
However, the explain plan is output as follows: QUERY PLAN I'd expect the index_files_on_basename index to be used. ORDER BY `files`.basename ASC LIMIT 40 OFFSET 0 LEFT JOIN `files` ON (`images_files`.`file_id` = `files`.`id`) LEFT JOIN `images_files` ON (`images`.`id` = `images_files`.`image_id`) `id` integer not null primary key autoincrement,ĬREATE UNIQUE INDEX `index_folders_on_path_unique` on `folders` (`path`) įoreign key(`parent_folder_id`) references `folders`(`id`)ĬREATE UNIQUE INDEX `index_files_on_parent_folder_id_basename_unique` on `files` (`parent_folder_id`, `basename`) ĬREATE INDEX `index_files_on_basename` on `files` (`basename`) įoreign key(`image_id`) references `images`(`id`) on delete CASCADE,įoreign key(`file_id`) references `files`(`id`) on delete CASCADE,Įdit: I omitted what I think to be a necessary index, which I have since added: CREATE INDEX `index_images_files_on_file_id` on `images_files` (`file_id`) Īlso changed index_files_on_parent_folder_id_basename to be a unique index.Įxecuting an EXPLAIN QUERY PLAN for the following query: SELECT DISTINCT `images`.`id`
`id` integer not null primary key autoincrement In sqlite, minimal schema is as follows: CREATE TABLE IF NOT EXISTS "images" (
0 kommentar(er)
