Re: Simple Query - No Join - But Temp. Table and file Sort :(
Heiko G. Wrote:
-------------------------------------------------------
> Hi Toasty,
>
> the last Example works fine for me :) Last Thing I
> dont get is that I have to set the index
> (group_field,where_field). In almost all other
> threads about indexes, the indexes are set like
> (where_field(s),group_field(s)) ?!
>
> Since iam kinda new to indexes and DB design I
> also used the kind (where_field(s),group_field(s))
> where I could which improved Performance in many
> cases. But is it possible that, if I would use
> indexes like (group_field,where_field), the
> performance would be even better ?
>
> I will try this in the other query, which I posted
> as Reply to Ravenous B.B., mabye this explain why
> it dont use a Index at all :)
>
> Thx
> Heiko
Yeah it does seem kind of backwards I know. Normally you're right, you'd put a multi-col index on (where fields, group or order fields). I think that in most cases that's still the way to go.
AFAIK this is just a special case where MySQL can just quickly jump/scan through all unique values of (in your case (lockey,loc)) and for each pull out a single max/min value for the correct evdate range. (max/min very easy to get from an index (see
http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html ) so I guess it just directly compares the max/min with the >= value you've provided and returns the max/min if it evaluates to true and nothing if it evaluates to false. )
TBH I'm just working from the manpages and doing a bit of guessing. I've not looked in the source or anything. (yet!)
HTH,
Toasty
-----------------------------------------
email: 'toasty'*3 at gmail