使用STI时索引在哪里？

I am using Rails and postgres.

我正在使用Rails和postgres。

I have a couple of models using STI and i was wondering where i should put indexes on the tables and why?

我有几个使用STI的模型，我想知道我应该在表上放置索引以及为什么？

For example lets say i have the following setup:

例如，假设我有以下设置：

class Comment < AR; end
class MovieComment < Comment; end
class MagazineComment < Comment; end


# Fake Comment Table
id:integer
title:string
body:text
type:string

Thanks!

谢谢！

2 个解决方案

#1

On the type field, if you want only one of MovieComment or MagazineComment. If you don't do that, you won't need the index here. I'm not sure if AR does use type every time, but just to make sure.

在类型字段上，如果您只想要一个MovieComment或MagazineComment。如果你不这样做，你不需要这里的索引。我不确定AR是否每次都使用类型，但只是为了确保。

Because the id field is a primary key, an index should already be there.

因为id字段是主键，所以索引应该已经存在。

If you want to query by both type and id make sure you have a combined index.

如果要按类型和ID进行查询，请确保您具有组合索引。

On the other fields: Depends what you query on, but I suppose you want to retrieve these only.

在其他字段：取决于您查询的内容，但我想您只想检索这些内容。

#2

In general, you need indices in the columns that you will use when performing queries.

通常，您需要在执行查询时使用的列中的索引。

If you do MovieComment.find(10), the database will use the index in the id field that Rails will add automatically for you. Same if you do Comment.find(30): Rails will retrieve the commend with id 30 using the index, then it will read the type column and it will return a MovieComment or a MagazineComment.

如果你做MovieComment.find（10），数据库将使用Rails将自动为你添加的id字段中的索引。如果你做的话也一样Comment.find（30）：Rails将使用索引检索id为30的推荐，然后它将读取type列，它将返回MovieComment或MagazineComment。

If you are going to add a feature to search by title, for instance, you will have to create an index in this column as well. In this case, probably a :fulltext index.

例如，如果要添加要按标题搜索的功能，则还必须在此列中创建索引。在这种情况下，可能是：全文索引。

An Index in the type column would make a query like MagazineComment.all faster because it is equivalent to Comment.where(type: 'MagazineComment').all, but it is probably not worth it.

类型列中的索引会使像MagazineComment.all这样的查询更快，因为它等同于Comment.where（类型：'MagazineComment'）。所有，但它可能不值得。

#1