MySQL在Having上使用索引吗？ [英] Does MySQL use indexes on Having?

问题描述

查询的一部分如下：

HAVING date > '2011-04-13 04:28:03'

索引了日期变量，是否有对查询有影响吗？

The date variable is indexed, does this have any effect on the query?

EXPLAIN EXTENDED似乎没有使用索引，但是我不知道那是否仅是因为我在数据库中有4行

EXPLAIN EXTENDED doesn't seem to be using the index, but I don't know if that's only because I have 4 rows in the database that I'm testing with.

我的查询：

SELECT AVG(slen) FROM
(
SELECT date, COUNT(id) as slen
FROM table
WHERE product_id = 2830
GROUP BY id
HAVING date > '2011-04-13 04:28:02'
) as T

有几行具有不同的日期值。我想选择日期>‘2011-04-13 04:28:02’的ID组。然后，我想要属于组的平均行数，没有日期条件。

There are a few rows that have different date values. I want to select groups of ID that have a date > '2011-04-13 04:28:02'. I then want the average number of rows that belong to a group, without the date condition.

查询本身不起作用

我的另一个担心是，日期>'2011-04-13 04:28:02'是否会使用我的日期列索引。

My other concern was whether the date > '2011-04-13 04:28:02' would use my date column index.

从此数据集中：

sid             datelast                product_id
782240551706    2011-04-13 00:51:52     2830
782240551706    2011-04-13 04:05:48     2830
782240551706    2011-04-13 04:28:03     2830
111111111111    2011-04-13 00:50:30     2830

所需结果：

应选择ID为782240551706的组，平均值应为3。

The group with id 782240551706 should be chosen, and the average should be 3.

以下查询将产生所需的结果：

The following query produces the desired result:

SELECT AVG(slen) FROM
(
SELECT date, COUNT(id) as slen
FROM table
WHERE product_id = 2830
GROUP BY id
HAVING **max(date)** > '2011-04-13 04:28:02'
) as T

推荐答案

例如：

SELECT 
    column1, 
    count(column2) as count 
FROM table 
GROUP BY column1 
HAVING count > 1

该计数由您的查询计算得出。您的情况未将其编入索引。您可以将having子句更改为where子句。

The count is calculated by your query. It's not indexed in your case. You can change the having clause to a where clause.

这篇关于MySQL在Having上使用索引吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！