SELECT * FROM table WHERE from_user=? AND to_user=? ORDER BY created_at DESC
created_at is a datetime column.
created_at是一个日期时间列。
According to my previous question (here), I learned that you don't need to create a descending index.
根据我之前的问题(这里),我了解到你不需要创建一个降序索引。
Is this index ok if I have billions of records? (notice I'm not specifying a desc index for created_at)
如果我有数十亿条记录,这个索引是否正常? (注意我没有为created_at指定desc索引)
>> from_user_id, to_user_id, created_at
1 个解决方案
#1
0
IMO having an index on from_user_id, to_user_id, created_at would not help you much.
I don't think you have many records with the same from_user_id, to_user_id, created_at values. (date time with millisecond!)
If my assumption is correct, in the index with such a low cardinality the effectiveness is reduced to a linear search plus wasting storage.
I will suggest having an index on from_user_id, to_user_id, matching your where criteria, the index will be applied before ordering.
In any case you can see the query execution plan to see the query cost, when having these indexes.
(indexing mechanism overview is interesting)
在from_user_id,to_user_id,created_at上有索引的IMO对你没什么帮助。我不认为你有很多具有相同from_user_id,to_user_id,created_at值的记录。 (以毫秒为单位的日期时间!)如果我的假设是正确的,在具有如此低基数的索引中,有效性将降低为线性搜索加上浪费存储。我建议在from_user_id,to_user_id上有一个索引,匹配你的where条件,索引将在订购之前应用。在任何情况下,您都可以看到查询执行计划,以查看具有这些索引的查询成本。 (索引机制概述很有趣)
#1
0
IMO having an index on from_user_id, to_user_id, created_at would not help you much.
I don't think you have many records with the same from_user_id, to_user_id, created_at values. (date time with millisecond!)
If my assumption is correct, in the index with such a low cardinality the effectiveness is reduced to a linear search plus wasting storage.
I will suggest having an index on from_user_id, to_user_id, matching your where criteria, the index will be applied before ordering.
In any case you can see the query execution plan to see the query cost, when having these indexes.
(indexing mechanism overview is interesting)
在from_user_id,to_user_id,created_at上有索引的IMO对你没什么帮助。我不认为你有很多具有相同from_user_id,to_user_id,created_at值的记录。 (以毫秒为单位的日期时间!)如果我的假设是正确的,在具有如此低基数的索引中,有效性将降低为线性搜索加上浪费存储。我建议在from_user_id,to_user_id上有一个索引,匹配你的where条件,索引将在订购之前应用。在任何情况下,您都可以看到查询执行计划,以查看具有这些索引的查询成本。 (索引机制概述很有趣)