[sqlite] creating indexes in attached databases

[Jonathan Ellis]

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 to [EMAIL PROTECTED]
-

[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,
   nametext 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]
-

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

[drh]

"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;

--
D. Richard Hipp   <[EMAIL PROTECTED]>


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

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?)

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

[Gerry Snyder]

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 = 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?)


Yes. It tells sqlite not to use those fields as an index.

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

Re: [sqlite] creating indexes in attached databases

[drh]

"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 this?
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> .



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

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

[Jonathan Ellis]

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 the subquery now.

So, now I've tried it with various combinations of + but I still can't
get the file name index used.

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

Re: [sqlite] creating indexes in attached databases

[Jonathan Ellis]

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 it.

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