在字段上建立索引并“按...排序”在另一个领域 [英] Indexing on a field and "order by" on another field

问题描述

假设我有一个小型数据库，其中包含三列： id1， id2和 date。
我在字段 id1上建立数据库索引，因为它是一个经常选择的字段（许多选择查询都使用ex来运行：其中 id1 = 125548）。

Let's assume I have a small database with three columns : "id1", "id2" and "date". I am indexing my database on the field "id1" since it is a frequently selected field (many select queries are ran with ex: where "id1" = 125548).

在某些特定查询中，我需要根据未建立索引的表中的日期字段对用户的记录进行排序。我的好奇心是排序操作（基本上是按顺序操作） ）上的日期字段将在整个数据库上运行，或仅在我根据 id1字段选择的用户行（例如125548）上运行。

In some particular query I have, I need to sort the records of a user based on the date" field in the table which is not indexed. My curiosity is if the sort operation (which is basically a order-by operation) on the date field will be ran on the whole database or only on the rows of the user (ex: 125548) which I select based on the "id1" field.

下面是一个示例查询：

SELECT u 
FROM UserView u 
WHERE u.viewedId=:viewedId AND u.viewDate >=:dateLimit 
ORDER BY u.viewDate DESC

推荐答案

首先，假设您的查询确实很简单，您可以创建同时执行这两个操作的索引：

FIrst, you can create an index that will do both, assuming your query really is simple:

select . . 
from table t
where id1 = X
order by col2

A comp table（id1，col2）上的osite索引可用于 where 和订单由。

A composite index on table(id1, col2) can be used for both the where and order by.

关于您的问题。通常，将首先进行过滤。可能有些数据库会查看该表的统计信息，并说天哪，一半的记录包含 id1 ，而我的索引是 col2 -我将使用索引进行排序。但是，没有数据库会对所有数据进行排序（而不是使用索引），然后再对其进行过滤。

As for your question. In general, the filtering will be done first. There may be some databases that would look at the statistics for the table and say "gosh, half the records have id1 and I have an index on col2 -- I'll use the index for the sort". However, no database would sort all the data (as opposed to using an index) and then filter it afterwards. Sorts are more efficient on smaller amounts of data.

编辑：

对于您的查询：

SELECT u 
FROM UserView u 
WHERE u.viewedId=:viewedId AND u.viewDate >=:dateLimit 
ORDER BY u.viewDate DESC;

最佳索引是 UserView（ViewId，viewDate）。假设 UserView 是一个表（或者可能是仅从一个表中选择的视图），则此索引应同时用于 WHERE （两个条款）和 ORDER BY 。

The optimal index is UserView(ViewId, viewDate). Assuming that UserView is a table (or perhaps a view that only selects from one table), then this index should be used for both the WHERE (both clauses) and the ORDER BY.

这篇关于在字段上建立索引并“按...排序”在另一个领域的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！