Laravel十进制列索引 [英] Laravel Decimal Column Indexing

问题描述

我在我的桌子'帖子'上使用了众所周知的reddit'hot'算法。现在这个热门列是一个十进制数字，如下所示：'XXXXX，XXXXXXXX'

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?

非常感谢你！

推荐答案

除非你需要约束，否则不要让它变得独一无二。

Do not make it UNIQUE unless you need the constraint that you cannot insert duplicates.

换句话说，一个 UNIQUE 键是两件事：一个 INDEX （用于快速搜索）和约束（在尝试插入重复时给出错误）。

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热DESC 可以使用 INDEX（热门）（或 UNIQUE（热门））。我说可以，而不是将，因为还有其他问题，优化器可能决定不使用索引。 （没有看到实际查询并了解有关数据集的更多信息，我不能更具体。）

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.)

如果 id 是 PRIMARY KEY ，然后这些都没有任何用处： INDEX（id，hot）; UNIQUE（id，hot）。交换列的顺序是有道理的。或者只是 INDEX（热门）。

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).

警告： EXPLAIN 没有说明索引是否用于 ORDER BY ，仅适用于 WHERE 。另一方面， EXPLAIN FORMAT = JSON 确实提供了更多细节。试试吧。

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.

（是的， DECIMAL 列可以编入索引。）

(Yes, DECIMAL columns can be indexed.)

这篇关于Laravel十进制列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！