Skip to content
This repository has been archived by the owner on Nov 22, 2022. It is now read-only.

Consider adding SQL indexes #6

Open
ngirard opened this issue Dec 3, 2020 · 4 comments
Open

Consider adding SQL indexes #6

ngirard opened this issue Dec 3, 2020 · 4 comments
Labels

Comments

@ngirard
Copy link
Contributor

ngirard commented Dec 3, 2020

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
Copy link
Owner

amoffat commented Dec 3, 2020

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
Copy link
Contributor Author

ngirard commented Dec 3, 2020

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 amoffat added good first issue Good for newcomers performance labels Dec 3, 2020
@amoffat
Copy link
Owner

amoffat commented Dec 3, 2020

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
Copy link
Contributor Author

ngirard commented Dec 3, 2020

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.

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
Projects
None yet
Development

No branches or pull requests

2 participants