3 ms·
I’m curious what the reasoning behind having no id or key column. Do you use indexed expressions to index JSON fields? Do you use Rowid for certain queries? Or
by actuallyalys 2y ago
I’m curious what the reasoning behind having no id or key column. Do you use indexed expressions to index JSON fields? Do you use Rowid for certain queries? Or do you not bother with indexes?
My understanding is that NoSQL databases still have indexes and it seems like using SQLite as you demonstrate could be worse in that regard.
- TekMol 2y agoreasoning behind having no id or key column The entries in the data column already have an id field. What would be an upside of having another id in a column? It seems that only complicates things and has the potential for invalid states (different id in the id column than in the json field). So far I have not used indexes because things are fast enough as they are. I would expect that when I need more speed, I can easily add indexes on expressions. Why do you expect SQLite indexes on expressions to be worse than what NoSQL databases do for indexing?
- actuallyalys 2y agoI 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. [0]: https://sqlite.org/expridx.html https://sqlite.org/expridx.html
- TekMol 2y agoWhat do you mean by "the item being indexed might be outright missing"? What is "item" here?