On Dec 9, 2009, at 12:58 PM, [email protected] wrote:
> The only SELECT is on MAX('timestamp'). There is really nothing to Group BY
> in this query.
>
> Dewey
>
> Philip Thompson wrote:
>> Hi.
>>
>> In a mysql query, it is recommended that "GROUP BY" fields be indexed? Using
>> EXPLAIN on one of my queries, I noticed no change between having indexed by
>> GROUP BY field and not indexing it. Any thoughts would be appreciated.
>>
>> In this example, should `history_field` be indexed...?
>>
>> SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND
>> `history_record_id` = 35
>> GROUP BY `history_field`
>>
>> Thanks,
>> ~Philip
Well, that was just an example query. My real one is....
SELECT `h`.*
FROM (
SELECT MAX(`history_timestamp`) AS `max_ts`
FROM `history`
WHERE `req_id` = 17 AND `history_record_id` = 35
GROUP BY `history_field`
) AS `max`
INNER JOIN `history` `h` ON `max`.`max_ts` = `h`.`history_timestamp`
WHERE `req_id` = 17 AND `history_record_id` = 35
GROUP BY `history_field`
This returns the results I need. The explain (split up) from this query is...
+----+-------------+------------+------+--------------------------------------------+
| id | select_type | table | type | possible_keys
|
+----+-------------+------------+------+--------------------------------------------+
| 1 | PRIMARY | h | ref |
req_id_history_record_id,history_timestamp |
| 1 | PRIMARY | <derived2> | ALL | NULL
|
| 2 | DERIVED | history | ref | req_id_history_record_id
|
+----+-------------+------------+------+--------------------------------------------+
--------------------------+---------+-------------+------+----------------------------------------------+
key | key_len | ref | rows | Extra
|
--------------------------+---------+-------------+------+----------------------------------------------+
req_id_history_record_id | 8 | const,const | 3 | Using temporary;
Using filesort |
NULL | NULL | NULL | 2 | Using where
|
req_id_history_record_id | 8 | | 3 | Using where; Using
temporary; Using filesort |
--------------------------+---------+-------------+------+----------------------------------------------+
3 rows in set (0.01 sec)
There's only 10 records in table right now... but the # of rows it's going to
traverse before find the results is very small.
Do I need to include `history_field` in the inner select?
Thanks,
~Philip
