我应该联合索引一个ActiveRecord多态关联？ [英] Should I joint-index an ActiveRecord polymorphic association?

问题描述

我有，我希望是非常大的一个指标表。它有一个多态关联，以便它可以belongs_to的是想记录一些指标等机型。我通常喜欢这个指数的关联列，以加快协会负荷。 <一href="http://$c$cshooter.word$p$pss.com/2008/09/27/handling-database-indexes-for-rails-polymorphic-associations/">I've听到人们在谈论联合索引该协会。这看起来像：

I have a metric table that I expect to be very large. It has a polymorphic association so that it can belongs_to other models that want to record some metric. I typically index association columns like this to speed up association loading. I've heard people talking about joint-indexing this association. This looks like:

add_index：评论，[：commentable_type，：commentable_id]

但我也听说商议，创造低基数的指标，因为该指数的收益并不能抵消保持它的开销。由于我的多态关联的_type上半年可能只有跨越数百万行4-5的价值观，我倾向于只指数的多态关联的_id部分。我将使用_id列和一些其他未提及的整数和datetime列可能创造一些额外的联合索引，但我将不包括在这些指标的_type无论是。

But I've also heard counsel against creating indexes of low-cardinality, because the payoff of the index doesn't offset the overhead of maintaining it. Since the _type half of my polymorphic association will probably only have 4-5 values across the millions of rows, I'm inclined to only index on the _id portion of the polymorphic association. I will probably create some additional joint-indexes using the _id column and some other unmentioned integer and datetime columns, but I won't include the _type in these indexes either.

这是你会做什么/建议？

Is this what you would do/recommend?

推荐答案

归根结底，这是值得基准之前和添加索引，在现实的数据集后 - 现实的大小和数据。

Ultimately, this is worth benchmarking before and after adding the index, on a realistic data set - realistic in size and data.

但是，你没有创建一个领域只有几个值的指数。该指数是对的组合的两个字段，这很可能具有不同的值的组合的一很多的的的。在合并领域的指数是一个聪明的想法。

However, you're not creating an index on a field with just a few values. The index is on the combination of the two fields, which is likely to have a lot of different value combinations. The index on the combined fields is a smart idea.

这篇关于我应该联合索引一个ActiveRecord多态关联？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！