I am using the commonly known reddit 'hot' algorithm on my table 'posts'. Now this hot column is a decimal number like this: 'XXXXX,XXXXXXXX'
I want this column to be an index, because when I order by 'hot', I want the query to be as fast as possible. However, I am kind of new to indexes. Does an index need to be unique?
If it has to be unique, would this work and be efficient?
$table->unique('id', 'hot');
If it does not have to be unique, would this be the right approach?
$table->index('hot');
Last question: would the following query be taking advantage of the index?
Post::orderBy('hot', 'desc')->get()
If not, how should I modify it?
Thank you very much!
Do not make it UNIQUE unless you need the constraint that you cannot insert duplicates.
Phrased differently, a UNIQUE key is two things: an INDEX (for speedy searching) and a "constraint" (to give an error when trying to insert a dup).
ORDER BY hot DESC can use INDEX(hot) (or UNIQUE(hot)). I say "can", not "will", because there are other issues where the Optimizer may decide not to use the index. (Without seeing the actual query and knowing more about the the dataset, I can't be more specific.)
If id is the PRIMARY KEY, then neither of these is of any use: INDEX(id, hot); UNIQUE(id, hot). Swapping the order of the columns makes sense. Or simply INDEX(hot).
A caveat: EXPLAIN does not say whether the index is used for ORDER BY, only for WHERE. On the other hand, EXPLAIN FORMAT=JSON does give more details. Try that.
(Yes, DECIMAL columns can be indexed.)