order by causes filesort

I still have following problem

EXPLAIN EXTENDED SELECT
  `item`.`id`,
  `item`.`timestamp`,
  `item`.`label`
  FROM
  item
WHERE
  item.dataTypeId=30
GROUP BY
  item.id
ORDER BY
  item.timestamp DESC
LIMIT 0,6;

Id & timestamp is a primary key pair (mediumint+datetime) dataTypeId is a foreign key (mediumint) table is created as InnoDb

There can be more records with same id and different timestamp (versio开发者_如何学编程ns of same item). This is the reason for group by.

I read for example this one: similar topic on stackoverflow

but it didnt solve my problem.

I've tried to create following indexes:

1. index on (dataTypeId, id, timestamp) - in that order
2. index on (dataTypeId, timestamp) - in that order
3. index on id
4. index on timestamp

the last two is a little piece of desperation

i think i must miss something basic -

but really do no know what.

Do not expect the solution (it would be nice :) just kick me the right way :)

sort_buffer_size is now 4194288

edit: explain - no indexes

"1" "SIMPLE"    "item"  "ref"   "FK_dataTypeId" "FK_dataTypeId" "4" "const" "5608"  "Using where; Using temporary; Using filesort"

explain with indexes created

"1" "SIMPLE"    "item"  "ref"   "FK_udssDataItem_1,testIndexType,testIndexTypeTimestamp,testIndexTypeIdTime"    "FK_udssDataItem_1" "4" "const" "5632"  "Using where; Using temporary; Using filesort"

---

There is an issue with your query. When you do the "group by id", you may have different timestamps for the same id and have not specified which one to use (Min(), max() etc) a similar problem occurs with the "label" field.

http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html

So you need agregate functions on timestamp and label otherwise the values returned may be unpredictable.

As you are grouping by id and the sorting by timestamp, so MySQL extracts one timestamp per group so an index doesn't really help much. You may not be able to get rid of the filesort with this query.

---

So your question is "How to avoid filesort on your query" ?
In which case, to get MySQL to do an index sort you need to have all of the columns in your index in your where clause.

With the id,timestamp primary key you have to

where id = myid and item.timestamp between (t1,t2)

Also beware of open-ended ranges (and timestamp < now() )

I'm not sure what datatypeID is, but if thats your only condition, then adding an index on just that column should also suggest an index sort. but you might have to create the index on (timestamp,datatypeID) ...in that order... instead.