MySQL Forums
Forum List  »  Optimizer & Parser

Simple Query - No Join - But Temp. Table and file Sort :(
Posted by: Heiko G.
Date: October 21, 2006 09:06AM

Hi All,

maybe can explain to my why mysql uses filesort and temp. table for this simple query:

SELECT e.evdate, e.lockey, e.loc
FROM sc_events e
WHERE e.evdate >=1161428363
GROUP BY e.lockey

This table looks like:

Feld Typ Attribute Null Standard Extra Aktion
id int(11) Nein auto_increment
lockey varchar(10) Nein
loc varchar(255) Nein
descr text Nein
image varchar(10) Nein
www varchar(200) Nein
title varchar(255) Nein
evdate bigint(10) Nein 0
evdate_end bigint(10) Nein 0
dj varchar(200) Nein
style varchar(60) Nein 0
entrmin varchar(4) Nein
entrmax varchar(4) Nein
owner int(5) Nein 0

Indexes:

Name Typ Kardinalität Aktion Feld
PRIMARY PRIMARY 2985 id
evdate_end INDEX 2985 evdate_end
loc
evdate_2 INDEX 2985 evdate
lockey
loc

..and explain shows me something like this:

table type possible_keys key key_len ref rows Extra
e index evdate_2 evdate_2 8 NULL 2985 Using where; Using index; Using temporary; Using filesort

Can i change the index, or th query to get rid of the filsort and temp table here ? Sorry for Cut & Paste, but it seems I cant use <pre> tags here :(

Thx in advance
Heiko

Options: ReplyQuote


Subject
Views
Written By
Posted
Simple Query - No Join - But Temp. Table and file Sort :(
4113
October 21, 2006 09:06AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.