[sqlite] indexes with ORDER BY on columns from different tables

[Jonathan Ellis]

When ordering by columns from two tables, sqlite isn't using the index
on the first column.

explain query plan SELECT *
FROM files f, file_info fi
WHERE f.id = fi.file_id
ORDER BY f.name, fi.mtime;

0|0|TABLE files AS f
1|1|TABLE file_info AS fi WITH INDEX info_by_file

If I drop the secondary order (to simply "ORDER BY f.name") then it
does use the index:

0|0|TABLE files AS f WITH INDEX files_by_name ORDER BY
1|1|TABLE file_info AS fi WITH INDEX info_by_file

Is there a workaround?  Here are sample tables:

CREATE TABLE files (
   id              integer PRIMARY KEY,
   name            text NOT NULL
);
CREATE INDEX files_by_name on files(name);

CREATE TABLE file_info (
   file_id         int NOT NULL REFERENCES files,
   mtime           int NOT NULL
);
CREATE INDEX info_by_file on file_info(file_id);

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------