问题描述
你好，我有一张模特照片:
Hello for one of my models photos I have:
default_scope :order => 'photos.created_at DESC, photos.version DESC'
鉴于我是按 CREATED_AT 和版本订购的...我应该在 CREATED_AT 上有一个数据库索引吗?
Given that I'm ordering by CREATED_AT and Version... Should I have a DB index on CREATED_AT?
谢谢
推荐答案
以下内容基于我的 PostgreSQL 经验，但可能也适用于 MySQL 和其他.
The following is based on my PostgreSQL experience but would probably also apply for MySQL and others.
如果您打算从此表中检索大量记录或使用分页，则ORDER BY 中使用的字段的索引将很有用.
If you plan on retrieving a large number of records from this table or making use of pagination, an index on the fields used in the ORDER BY would be useful.
您应该以相同的顺序在所有订单字段上创建索引.如果您在 ORDER BY 中混合使用 ASC 和 DESC，则需要使用这些特定顺序创建索引以充分利用索引.
You should create an index on all order fields in the same order. If you are mixing ASC and DESC in your ORDER BY you would need to create an index with these specific orderings to take full advantage of the index.
适合您的照片表的 ActiveRecord 迁移是:
A suitable ActiveRecord migration for your photos table would be:
add_index :photos, [:created_at, :version]
我建议在添加索引之前和之后查看带有类似生产数据的 EXPLAIN ANALYZE 输出，以确认它具有您想要的效果.
I would recommend looking at the EXPLAIN ANALYZE output with production-like data before and after adding the index to confirm it is having the effect you are after.
这篇关于rails - created_at 当用户订购时，是否应该向表中添加索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！