Database schema changes
Nobody has claimed this yet.
Assessment
- Difficulty
- 5/5
- Estimated time
- Over a week
- Newbie friendliness
- 25/100
Research direction
Start with docs/setup.sql and reproduce the reported SELECT against a large items table to understand the current schema and query cost. Review the feeds, items, and subscriptions definitions, then trace the application code that uses feedUrl; done means the integer feedId migration and related queries remain synchronized across all three tables.
Written by the indexing model from the issue text.
Description
Seeing that a database with millions of rows in the items table, can get queries to slow down, specifically this one:
SELECT * FROM items WHERE flDeleted = FALSE AND feedUrl IN (SELECT feedUrl FROM subscriptions WHERE listName='dave') ORDER BY pubDate DESC LIMIT 175;
Which can take ~10 seconds to load, I propose we switch from using feedUrl as the primary key to an integer based key, which will help speed up the query significantly. Testing locally, the same query took about ~20ms after the switch.
Maybe for such a big change, we might as well review the setup.sql file. The following tables use feedUrl as primary key and should be changed:
- feeds
- items
- subscriptions
But this will also involve code changes to keep in sync the new feedId with the current feedUrl and querying feedId instead.
- Dominant language
- HTML
- Stars
- 11
- Forks
- 4
- PR merge metrics
- No merged PRs in 30d
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from scripting/feedlandInstall
-
Difficulty 2/5 1-3 hours Newbie friendliness 64/100
scripting/feedlandInstall#75 · 3 comments · 1 reaction ·
-
Difficulty 4/5 3-5 days Newbie friendliness 55/100
scripting/feedlandInstall#79 · 1 comment · 1 reaction ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 55/100
scripting/feedlandInstall#77 · 3 comments ·
-
Difficulty 4/5 3-5 days Newbie friendliness 35/100
scripting/feedlandInstall#76 · 3 comments ·
-
Difficulty 1/5 Under an hour Newbie friendliness 20/100
scripting/feedlandInstall#74 · 3 comments ·
All issues in scripting/feedlandInstall
Similar issues
-
bug
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
avniproject/avni-client#2135 ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 88/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
use-agent-os/agent-os#3276 ·
-
enhancement
Difficulty 2/5 1-3 hours Newbie friendliness 88/100
-
good first issue refactor
Difficulty 2/5 1-3 hours Newbie friendliness 72/100