Re: Group by
| From: | Philip Thompson | Date: | Wed, 09 Dec 2009 19:49:44 +0000 |
| Subject: | Re: Group by | ||
| References: | 1 2 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-46671@lists.php.net to get a copy of this message | ||
On Dec 9, 2009, at 12:58 PM, host@deweywilliams.com 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