MySQL Forums
Forum List  »  Optimizer & Parser

Re: Simple Query - No Join - But Temp. Table and file Sort :(
Posted by: Heiko G.
Date: October 22, 2006 04:56AM

Hi,

thc for your answer, i tried what u suggested and now i *only* have to life with the temporary table. Is there some easy Rule when mysql has to use file sorts or temp tables ?! I have a similar problem with som eother table where it scans the whole table, no index is used (tried to use a index for (xsite,ysizr,gid), nor Do I use a join :( I dont understand why mysql has to scan the whole table to simple fetch one record per (gid)

SELECT name, gid, xsize, ysize
FROM sc_pictures
WHERE xsize > ysize
GROUP BY gid

table  type  possible_keys  key  key_len  ref  rows  Extra  
sc_pictures ALL NULL NULL NULL NULL 38231 Using where; Using temporary; Using filesort

It seems to me this is the same problem as in my other query. I use GROUP but no MAx, MIN etc..The reason is, I simple want (name, gid, xsize, ysize) of *any* picture where x > y , and since I only want one Record per gallery (gid) I used this GROUP by...i had no problems with it so far...at least i did not notice anything, but this Query takes 0.08 seconds, if i remove the group it runs for 0.0002 seconds...I will try to find some workaround

will try some stuff
Thx again Guys!!

Heiko

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Simple Query - No Join - But Temp. Table and file Sort :(
2963
October 22, 2006 04:56AM


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.