[sqlite] creating indexes in attached databases

sqlite> attach 'foo.db' as foo; sqlite> create table foo.bar (i int); sqlite> create index foo.bar_i on foo.bar(i); SQL error: near ".": syntax error sqlite> Is there another way to do this? - To unsubscribe, send email

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

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

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

"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

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

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

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

Jonathan Ellis wrote: 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 =

Re: [sqlite] creating indexes in attached databases

"Jonathan Ellis" <[EMAIL PROTECTED]> wrote: > sqlite> attach 'foo.db' as foo; > sqlite> create table foo.bar (i int); > sqlite> create index foo.bar_i on foo.bar(i); Should be: create index foo.bar_i on bar(i); > SQL error: near ".": syntax error > sqlite> > > Is there another way to do

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

On 8/19/06, Gerry Snyder <[EMAIL PROTECTED]> wrote: Jonathan Ellis wrote: > (was the "+" some kind of shorthand I'm unfamiliar with?) > Yes. It tells sqlite not to use those fields as an index. Ah, I see. When I tried "+f.mtime" it gave a "no such column" error, but that was because f was in

Re: [sqlite] creating indexes in attached databases

On 8/19/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Jonathan Ellis" <[EMAIL PROTECTED]> wrote: > sqlite> attach 'foo.db' as foo; > sqlite> create table foo.bar (i int); > sqlite> create index foo.bar_i on foo.bar(i); Should be: create index foo.bar_i on bar(i); Thanks, that fixed