List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:May 27 2011 7:26pm
Subject:Using where; Using temporary; Using filesort
View as plain text  
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 filesorthsv31 May