Re:Collection of insane and fun facts about SQLite
by actuallyalys on Tuesday December 31, 2024 @01:24PM
(#42560650)
I wouldn’t suggest duplicating the id field, but moving from the data field.
> Why do you expect SQLite indexes on expressions to be worse than what NoSQL databases do for indexing?
I don’t have a concrete reason. It’s just that indexes on expressions are not intended as the main index for a SQLite database to my knowledge. Having thought about it more and read the page on expression indexes more thoroughly[0], I think it’s probably unlikely to be a noticeable downside.
The only remaining downside is that the item being indexed might be outright missing, but it sounds like that’s basically a feature in your case, as you’re opting for flexibility.
> Why do you expect SQLite indexes on expressions to be worse than what NoSQL databases do for indexing?
I don’t have a concrete reason. It’s just that indexes on expressions are not intended as the main index for a SQLite database to my knowledge. Having thought about it more and read the page on expression indexes more thoroughly[0], I think it’s probably unlikely to be a noticeable downside.
The only remaining downside is that the item being indexed might be outright missing, but it sounds like that’s basically a feature in your case, as you’re opting for flexibility.
[0]: https://sqlite.org/expridx.html