This repository was archived by the owner on Sep 9, 2025. It is now read-only.
Repository navigation
Storage: Add Indexes for common queries - #308
Merged
Merged
Conversation
In github#306 it's reported that SQLite is slow. This adds indexes so lookups will be faster. The intention is to speed up the bottom 3 queries from github#306 (comment) as they are "the ones to focus on". Specifically this should speed up: > * Load graph data for a file > > SELECT value FROM graphs WHERE file = ? > > Called many times during path stitching > > * Load paths for a file node > > SELECT file,value from file_paths WHERE file = ? AND local_id = ? > > Called many times during path stitching. > > * Load paths for root node > > SELECT file, value from root_paths WHERE symbol_stack = ? > > Called many times during path stitching. If the data that you're fetching is in the index then SQLite won't even bother reading the row proper, just pulling the data streight from the index (good for data locality). As such I've included not just the columns we're using in the `WHERE` clause, but also the ones from `SELECT` too. I've not actually tested the performance impact as I'm not familiar with this project (this is more of a drive-by) and don't know what benchmarks to use.
Contributor
|
This is great! I tried it locally and it seems to speed up things quite a bit. I've made a few adjustments:
|
hendrikvanantwerpen
approved these changes
Aug 9, 2023
hendrikvanantwerpen
left a comment
hendrikvanantwerpen
left a comment
Contributor
There was a problem hiding this comment.
Tested locally by indexing typeorm/typeorm and running a query against it. Query response came in significantly faster than without the index.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to subscribe to this conversation on GitHub.
Already have an account?
Sign in.
2 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
In #306 it's reported that SQLite is slow. This adds indexes so lookups will be faster. The intention is to speed up the bottom 3 queries from #306 (comment) as they are "the ones to focus on".
Specifically this should speed up:
If the data that you're fetching is in the index then SQLite won't even bother reading the row proper, just pulling the data streight from the index (good for data locality). As such I've included not just the columns we're using in the
WHEREclause, but also the ones fromSELECTtoo.I've not actually tested the performance impact as I'm not familiar with this project (this is more of a drive-by) and don't know what benchmarks to use. The only testing I've done is
cargo test --features storage.