Daevid Vincent Using where; Using temporary; Using filesort
May 27, 2011; 12:26
Daevid Vincent
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.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=listsearcharchive@lassosoft.com
May 31
Hal?sz S?ndor Re: Using where; Using temporary; Using filesort
May 31, 2011; 11:04
Hal?sz S?ndor
Re: Using where; Using temporary; Using filesort
May 31
Daevid Vincent Using where; Using temporary; Using filesort
May 31, 2011; 11:27
Daevid Vincent
Using where; Using temporary; Using filesort
May 31
Peter Brawley Re: Using where; Using temporary; Using filesort
May 31, 2011; 13:37
Peter Brawley
Re: Using where; Using temporary; Using filesort
Feb 23
Daevid Vincent USING WHERE; USING TEMPORARY; USING filesort
Feb 23, 2012; 14:50
Daevid Vincent
USING WHERE; USING TEMPORARY; USING filesort
Feb 24
Johan De Meersman Re: USING WHERE; USING TEMPORARY; USING filesort
Feb 24, 2012; 12:17
Johan De Meersman
Re: USING WHERE; USING TEMPORARY; USING filesort
Feb 24
Perrin Harkins Re: USING WHERE; USING TEMPORARY; USING filesort
Feb 24, 2012; 10:20
Perrin Harkins
Re: USING WHERE; USING TEMPORARY; USING filesort
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!