Re: Group by

Philip Thompson wrote:

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.

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/