Simple query still it needs Using temporary; Using filesort ??

MySQL & MariaDB Other MySQL® Questions
1

HI,

My query is like this:

SELECT tag.* FROM tag LEFT JOIN book_item ON tag.Tag_ID = book_item.BookItem_ID WHERE tag.Tag = “tags” GROUP BY tag.tag_ID

Indexes:

Table ‘tag’:Keyname Type Cardinality FieldPRIMARY PRIMARY 203 Tag_IDTag INDEX 203 TagItem_ID INDEX 29 Item_IDTable ‘book_item’:Keyname Type Cardinality FieldPRIMARY PRIMARY 40 BookItem_ID

Explain:

id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE tag ref Tag Tag 92 const 6 Using where; Using temporary; Using filesort1 SIMPLE book_item eq_ref PRIMARY PRIMARY 4 makeliterature.tag.Tag_ID 1 Using index

My question is: how can I optimize this query to get rid of Using temporary; Using filesort ?

Even having in mind small size of dataset, what for on earth MySql and InnoDB need a temporary table to process this query ?

Thank you.

2
[B]stacionari wrote on Mon, 31 March 2008 23:37[/B]
My query is like this
The query you posted has a LEFT JOIN on a table that isn't referenced anywhere else, so I guess your actual query is different from the one you published. Any optimization would only apply to the query you published and would therefore be useless to you.

You should post an actual query if you want to receive useful help.