Does MySQL use indexes on Having?

Question

A portion of my query looks like:

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

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

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.

My query:

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

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.

The query as it is, does not work yet by the way.

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

From this dataset:

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

Desired Result:

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

Any reason to use `HAVING` instead of `WHERE`? – zerkms Apr 14 '11 at 02:42 — zerkms, Apr 14 '11 at 02:42
Can you show us a sample of what you expect for a result? – dkretz Apr 14 '11 at 02:51 — dkretz, Apr 14 '11 at 02:51

score 1 · Answer 1 · answered Apr 14 '11 at 02:29

1

HAVING is used in conjunction with a GROUP BY, so it's a derived table. I don't see how there would be an index to use.

answered Apr 14 '11 at 02:29

Joshua Martell

7,074
2
30
37

Does this mean that if I want to check that each group is in a certain date range, it will have to scan every row to qualify the HAVING condition? – Tony Apr 14 '11 at 02:31
I think it would be easier to help if you could post more information about what you're trying to do. I think you want to use the date condition before the GROUP BY clause. – Joshua Martell Apr 14 '11 at 02:33

score 1 · Answer 2 · edited Feb 27 '15 at 17:00

1

For example:

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

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

edited Feb 27 '15 at 17:00

Bryce Siedschlaw

4,136
1
24
36

answered Apr 14 '11 at 03:10

neocanable

5,293
2
23
28

score -1 · Answer 3 · answered Apr 14 '11 at 05:56

That query does not make sense, as the column "date" is in a GROUP BY, but is neither an aggregate nor GROUP BY clause.

Any normal SQL database would reject it; mysql would reject it if in strict mode.

The behaviour is basically undefined in this case. Don't do it.

I'm pretty sure you want WHERE to filter your rows, not HAVING. And you don't want to specify date in the column list, try min(date) or max(date) instead.

And no, HAVING won't use an index because it needs to perform the GROUP BY to find the groups which HAVING matches. Normally it is better to use WHERE, and you'd only use HAVING on an aggregate (e.g. HAVING COUNT(*) > 1)

Does MySQL use indexes on Having?

3 Answers3

Linked