Does MySQL use indexes if available?

Question

Part of my query looks like this:

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

The date variable is specified, does this affect the request?

EXPLAIN EXTENDED doesn't seem to use an index, but I don't know if it's just because I have 4 rows in the database I'm testing with.

My request:

 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 several lines that have different date values. I want to select groups of identifiers that have a date> '2011-04-13 04:28:02'. Then I want the average number of rows to belong to the group, without a date condition .

The request as is, until it works.

My other problem was whether the date> '2011-04-13 04:28:02' would use the index of the date columns.

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 the identifier 782240551706 should be selected, and the average should be 3.

The following query gives 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 

Answer 1

HAVING is used in conjunction with GROUP BY, so this is a view. I do not see how the index will be used.

Answer 2

For instance:

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

An invoice is calculated at your request. It is not indexed in your case. You can change the having clause to a where clause.

Answer 3

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

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

In this case, the behavior is mostly undefined. Do not do that.

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

And no, HAVING will not use the index because it needs to do GROUP BY to find groups that match HAVING. It's usually better to use WHERE, and you should use HAVING only for the aggregate (e.g. HAVING COUNT (*)> 1)