Re: [sqlite] Query planner: Covering index not chosen over primary key
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
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
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
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