News for hackers. Stuff that matters.
Re:Collection of insane and fun facts about SQLite by actuallyalys on Monday December 30, 2024 @08:02PM (#42555351)
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.

Re:Collection of insane and fun facts about SQLite by TekMol on Tuesday December 31, 2024 @03:28AM (#42557357)

    reasoning 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?

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.

[0]: https://sqlite.org/expridx.html

Re:Collection of insane and fun facts about SQLite by TekMol on Wednesday January 01, 2025 @12:40PM (#42567571)
What do you mean by "the item being indexed might be outright missing"? What is "item" here?