Re: [sqlite] Query planner: Covering index not chosen over primary key

[Jen Pollock]

Thank you for the suggestion!

The actual schema & query are a good deal more complicated, and I'm not
looking for general optimization help with them right now.

Jen Pollock

On Fri, May 03, 2019 at 10:11:04PM +0100, Simon Slavin wrote:
> On 3 May 2019, at 9:34pm, Jen Pollock  wrote:
> 
> >  SELECT filename
> >  FROM images
> >JOIN embedded_files ON images.file_id == embedded_files.id
> >  WHERE type == 'png';
> 
> Try this:
> 
> CREATE INDEX images (type, file_id);
> ANALYZE;
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query planner: Covering index not chosen over primary key

[Simon Slavin]

On 3 May 2019, at 9:34pm, Jen Pollock  wrote:

>  SELECT filename
>  FROM images
>JOIN embedded_files ON images.file_id == embedded_files.id
>  WHERE type == 'png';

Try this:

CREATE INDEX images (type, file_id);
ANALYZE;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query planner: Covering index not chosen over primary key

[Richard Hipp]

On 5/3/19, Jen Pollock  wrote:
> I assume the problem here is that the primary key is usually a weird
> thing to index. I can definitely work around this, but I thought it
> might be worth reporting as something that could perhaps be improved in
> the query planner.

Thank you.  I have your request.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query planner: Covering index not chosen over primary key

[Jen Pollock]

I have a database with a schema roughly like this:

  CREATE TABLE embedded_files(
id INTEGER PRIMARY KEY,
filename TEXT,
data BLOB
  );
  CREATE TABLE images(
id INTEGER PRIMARY KEY,
file_id INTEGER,
type TEXT,
FOREIGN KEY(file_id) REFERENCES embedded_files(id)
  );

The following query is slow:

  SELECT filename
  FROM images
JOIN embedded_files ON images.file_id == embedded_files.id
  WHERE type == 'png';

Part of the problem is that many of the values in embedded_files.data
are quite large. I tried to improve the query's performance by creating a
covering index:

  CREATE INDEX embedded_files_id_filename ON embedded_files(id, filename);

However, the query planner won't use this index unless I force it to
with INDEXED BY. Forcing it to use the index does speed up the query.

I assume the problem here is that the primary key is usually a weird
thing to index. I can definitely work around this, but I thought it
might be worth reporting as something that could perhaps be improved in
the query planner.

Jen Pollock

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users