时间戳

首页 > 解决方案 > An indexed MySQL query performs better with a 1 than a 0

mysql - An indexed MySQL query performs better with a 1 than a 0

问题描述

I have the following query where Date has data-type non-nullable date-time, GroupingId has data-type non-nullable int and IsCompleted has data-type non-nullable tinyint(1).

SELECT
    *
FROM
    Table
WHERE
    Date < @TimeNow
AND 
    GroupingId = @GroupingId
AND 
    IsCompleted = 0;

I have an index on this table of GroupingId, Date, IsCompleted in that order.

For some reason if I run this query with t.IsCompleted = 0, it performs a lot slower than t.IsCompleted = 1 every time.

I'm thinking that it may not be indexed effectively but could do with some help on it.

EDIT

I've updated the example query to make it a lot clearer. When IsCompleted = 0 is set, it returns far fewer rows and takes a lot longer than when IsCompleted = 1 is set

标签： mysqlsqlindexing

解决方案

您的查询不能使用索引进行排序。因此，查询的性能将由匹配where条件的行数驱动。据推测，拥有的项目IsCompleted = 0多于IsCompleted = 1。

此查询的更好索引是(groupingId, isCompleted, date). 前两个键可以按任意顺序排列。

这个条件：

((t.Date >= @StartDate AND t.Date < @EndDate) OR (t.Date < @TimeNow))

也有点奇怪。我希望大多数或所有日期都在过去。假设它@TimeNow代表当前日期，这将返回所有行。

推荐阅读