Announcement Module
No announcement yet.

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

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

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


    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


    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


    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
    stacionari wrote on Mon, 31 March 2008 23:37

    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.