List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:May 31 2011 6:37pm
Subject:Re: Using where; Using temporary; Using filesort
View as plain text  
 > Is it ALWAYS possible to fabricate a query/schema in
 > such a way that MySQL ALWAYS uses the ideal

No. Optimisation is better in 5.6 than in 5.0, though.

Did you try adding multi-column indexes to cover the join and the order 
by clause?

 > 'Using where' extra -- you just have to keep at it?
 > Or is it the case that sometimes you're just S.O.L

I don't know a general answer to that question. To figure out the answer 
in a particular case, I usually have to see the Create Table statements, 
see how the query performs with representative data, and experiment with 
various index setups.

PB

-----

On 5/31/2011 1:27 PM, Daevid Vincent wrote:
> I sent this Friday, but it never made it to the list?!
>
> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@stripped]
> Sent: Friday, May 27, 2011 12:27 PM
> To: mysql@stripped
> Subject: Using where; Using temporary; Using filesort
>
> I'm trying to optimize a query that doesn't seem all that complicated,
> however I can't seem to get it to not use a temp table and filesort.
>
> developer@vm_vz_daevid:~$ mysql --version
> mysql  Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using  5.2
>
>
> EXPLAIN EXTENDED
> SELECT
> 	-- d.date_release,
> 	-- d.dvd_title,
> 	-- s.type,
> 	-- s.id_place,
>          s.scene_id AS index_id,
>          s.dvd_id
> FROM
>          dvds AS d JOIN scenes_list AS s
>          ON s.dvd_id = d.dvd_id
>          AND d.status = 'ok'
>          AND d.date_release != '0000-00-00'
> ORDER BY d.date_release DESC,
>          d.dvd_title ASC,
>          s.type ASC,
>          s.id_place ASC;
>
>
> *************************** 1. row ***************************
>             id: 1
>    select_type: SIMPLE
>          table: d
>           type: ref
> possible_keys: PRIMARY,date_release,status,status_release
>            key: status_release
>        key_len: 1
>            ref: const
>           rows: 1976
>          Extra: Using where; Using temporary; Using filesort
> *************************** 2. row ***************************
>             id: 1
>    select_type: SIMPLE
>          table: s
>           type: ref
> possible_keys: dvd_id_2,dvd_id
>            key: dvd_id
>        key_len: 4
>            ref: videoszcontent.d.dvd_id
>           rows: 6
>          Extra: Using where
> 2 rows in set, 1 warning (0.00 sec)
>
> There are proper indexes on most every column in both tables (as you can see
> there).
>
> [a] the EXTENDED keyword doesn't seem to do anything different? I get the
> same columns and results??!
>
> [b] The commented out columns above I thought might help with the ORDER BY
> for some reason from my reading here:
> http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
> they did not.
>
> [c] lopping off the ORDER BY all together stops the "Using temporary; Using
> filesort" of course. Yeah! But now I'm left with a table of data in random
> order. Re-sorting it in PHP seems like an even bigger waste of cycles, when
> no doubt MySQL can sort hella-faster.
>
> [d] just doing " ORDER BY d.date_release DESC, d.dvd_title ASC; ", prevents
> the "using temporary" but still does "filesort" and again I'm in the boat of
> [c]
> 	
> I guess my question is this: Is it ALWAYS possible to fabricate a
> query/schema in such a way that MySQL ALWAYS uses the ideal 'Using where'
> extra -- you just have to keep at it? Or is it the case that sometimes
> you're just S.O.L. and no matter what, MySQL is going to give you a
> Cleveland Steamer? In other words, am I wasting my time trying to tweak my
> query and indexes here with the idea there's some magic incantation that
> will get this "right" or do I just have to accept it is what it is and it's
> not going to do any better.
>
> d.
>
>
Thread
Using where; Using temporary; Using filesortDaevid Vincent31 May
  • Re: Using where; Using temporary; Using filesortPeter Brawley31 May