Consider adding SQL indexes

[ngirard]

trafficstars

Unless I'm mistaken, the code doesn't seem to create SQL indexes, and a quick look at the queries leaves me under the impression that they would benefit from indexes.

Any thoughts ?

[amoffat]

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

[ngirard]

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

[amoffat]

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

[ngirard]

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.