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

[Jonathan Ellis]

On 8/19/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

"Jonathan Ellis" <[EMAIL PROTECTED]> wrote:
> 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;
>
>
> Is there a workaround?

Try this:

  SELECT * FROM ....
  ORDER BY +f.name, +fi.mtime;

Doesn't seem to help, if I've understood correctly:

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

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

(was the "+" some kind of shorthand I'm unfamiliar with?)