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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
