Re: [PHP-DB] Group by

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