Query planner: Covering index not chosen over primary key

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query planner: Covering index not chosen over primary key

Richard Hipp-3
On 5/3/19, Jen Pollock <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Query planner: Covering index not chosen over primary key

Simon Slavin-3
In reply to this post by Jen Pollock
On 3 May 2019, at 9:34pm, Jen Pollock <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users