Hi @rhymes
,
Great post. TIL about the limit on size of what can be indexed. A buffer page is generally around 8kb so looks like 2.5 kb is maximum text size to be indexable.
I had a few queries on this:
Are articles queried directly by their text in the code? If not so, is a unique index needed for the article text?
The order of columns in the index matters. So, is there advantage in indexing (hash, title, user) vs (user, title, hash) as any query selecting by user will automatically use this index.
I realized there is a limit to title of 128 char in dev.to. Is that application only or a DB contraint/data type?
Finally, is there any advantage of adding the hash as a separate column and indexing it instead and adding the uniqueness of this column in Rails model like validates article_hash, uniqueness: true
No, it's just used to avoid accidental double posting mostly
It's not queried so it doesn't really matter in this case but yeah, in general it does matter, great point!
We don't have length CHECK constraints in the database, so it's one of those things that it's still only at the application level
It would mean that the hash needs to be updated at the code level at each "article save", which means that we're back to depending on Rails to check uniqueness
Hope this clears a bit :) Great questions!
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Hi @rhymes ,
Great post. TIL about the limit on size of what can be indexed. A buffer page is generally around 8kb so looks like 2.5 kb is maximum text size to be indexable.
I had a few queries on this:
validates article_hash, uniqueness: trueHi Raunak!
CHECKconstraints in the database, so it's one of those things that it's still only at the application levelHope this clears a bit :) Great questions!