Comment by TekMol | Hacker News Reader

Comment by TekMol | Hacker News Reader

Parent Full thread

    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?

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

What do you mean by "the item being indexed might be outright missing"? What is "item" here?

Reply on news.ycombinator.com