Philip Thompson wrote:
On Dec 9, 2009, at 12:58 PM, host@xxxxxxxxxxxxxxxxx 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.
You won't with only 10 rows in the table.
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`
I'd index
req_id, history_record_id, history_field, timestamp
If you're using myisam tables then all the data can be fetched directly
from the index instead of hitting the data table as well. If you're
using innodb, it'll at least use this index to search for req_id and
history_record_id (assuming these fields are normally in your queries).
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.
In theory. Sometimes databases don't work that way and instead of
choosing a particular index you'd expect it to, it'll pick another one.
Fill up the table(s) and make sure it does what you expect. Mysql isn't
great at subselects either.
Do I need to include `history_field` in the inner select?
No, you don't have to.
You could do a query like:
select count(id) from table group by another_field;
so you get a count per another_field of how many records there are. Not
a great example as normally you would include another_field in the
select, but you don't have to.
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
