Consider adding SQL indexes about supertag HOT 4 OPEN

Hi, adding a UNIQUE constraint to a column will create a unique index behind the scenes. Same with PRIMARY KEY designations. But now that you mention the indexes, I am not sure about FOREIGN KEY columns...can you find out if they create indexes? If not, we should add them

from supertag.

I am not sure about FOREIGN KEY columns...can you find out if they create indexes?

They don't.
According to this reference page:

In most real systems, an index should be created on the child key columns of each foreign key constraint. The child key index does not have to be (and usually will not be) a UNIQUE index.

Cheers

from supertag.

Thanks! There should definitely be an improvement if indexes are added for the FKs. Good catch.

I will leave this issue open for a bit, since is a straightforward problem to tackle for people interested in contributing to Supertag. It will likely just involve adding a new migration file (named m1.mod) here with the relevant schema changes

from supertag.

The queries from both link_file_to_tag() and remove_devicefile() would benefit from indexes on files.device and files.inode, i.e.

CREATE INDEX files_device_ix ON files(device);
CREATE INDEX files_inode_ix ON files(inode);

Also the queries against pins.tag_ids don't seem to be currently optimized. By the way, I'm not sure I understood your comments around the creation of the pins table: you're mentionning using FTS5, but the code doesn't seem to actually leveraging it.

from supertag.