Re: USING WHERE; USING TEMPORARY; USING filesort
- Original Message - From: Daevid Vincent dae...@daevid.com d.`date_release`!='-00-00' AND sd.`scene_id`=s.`scene_id` GROUP BY sg.`scene_id` ORDER BY num DESC, sd.`count_dl_monthly` DESC LIMIT 30; Could you put an index on sg.scene_id? Not sure, but it might help the grouping be more efficient. In general, though, you mqy be running into the limit of how big a temporary result table in memory can get before it gets moved to disk. There's two parameters that govern that iirc, max_tmp_table_size and another. Have a look at the manual. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: USING WHERE; USING TEMPORARY; USING filesort
On Thu, Feb 23, 2012 at 5:50 PM, Daevid Vincent dae...@daevid.com wrote: Anyone have any thoughts on how I might optimize this query? As always, it's all about the indexes. The index it chose on your main table looks pretty weak. You probably should move those non-joining columns out of your join condition in case their location is influencing the plan, and try experimenting with multi-column indexes. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
USING WHERE; USING TEMPORARY; USING filesort
Anyone have any thoughts on how I might optimize this query? It takes about
2 seconds. I know that seems quick, but we get nearly 30,000 hits per second
and so if we can shave this down, it helps. Also we do use memcache, but
even with that, we still see this in the slow-log sometimes. I have indexes
on everything used in this query and even a compound one as you see in the
EXPLAIN.
I'm not going to lose sleep over it, but I thought if there was something
simple or a way to refactor I'd give it a shot. I thought changing the query
to use JOIN ON syntax would have helped, but it didn't do anything really?!
Also, this is all being used with PHP, so I'm fine with pulling things out
into two or three queries if you suggest it will make a faster difference.
-- old query:
SELECT sg.`scene_id`, COUNT(*) AS num FROM `scenes_list` AS s, `dvds` AS d,
`scenes_genres` AS sg, `scenes_downloads_new` AS sd WHERE sg.`genre_id` IN
('1','8','10','19','38','58','65') AND d.`dvd_id`=s.`dvd_id` AND
sg.`scene_id`=s.`scene_id` AND d.`status`='ok' AND
d.`date_release`!='-00-00' AND sd.`scene_id`=s.`scene_id` GROUP BY
sg.`scene_id` ORDER BY num DESC, sd.`count_dl_monthly` DESC LIMIT 30;
-- refactored: is this correct order?? Smallest table and most filters first
right to narrow the dataset as quick as possible?
-- EXPLAIN
SELECT
s.`scene_id`,
COUNT(*) AS num
FROM`dvds` AS d
JOIN `scenes_list` AS s ON d.`dvd_id` = s.`dvd_id`
AND d.`date_release` != '-00-00'
AND d.`status` = 'ok'
JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id`
AND sg.`genre_id` IN ('1', '8', '10', '19', '38', '58',
'65')
JOIN `scenes_downloads_new` AS sd ON sd.`scene_id` = s.`scene_id`
GROUP BY sg.`scene_id`
ORDER BY num DESC, sd.`count_dl_monthly` DESC
LIMIT 30
;
SELECT COUNT(*) FROM dvds; -- 12181
SELECT COUNT(*) FROM scenes_downloads_new; -- 66054
SELECT COUNT(*) FROM scenes_list; -- 67197
SELECT COUNT(*) FROM scenes_genres; -- 344272
-- why do I still hit the filesort and temp table? how can I get rid of
that?
id select_type TABLE TYPEpossible_keys
KEY key_len ref ROWS Extra
-- --- -- --
-- --
--- - --
--
1 SIMPLE d ref
PRIMARY,date_release,STATUS,status_release,dvd_release status_release 1
const2436 USING WHERE; USING TEMPORARY; USING
filesort
1 SIMPLE s ref PRIMARY,dvd_id_2,dvd_id
dvd_id 4.d.dvd_id 6 USING WHERE
1 SIMPLE sd eq_ref PRIMARY
PRIMARY 3.s.scene_id 1 USING WHERE
1 SIMPLE sg ref PRIMARY,scene_id,genre_id
scene_id4.s.scene_id 5 USING WHERE
If I take off the ORDER BY (just to see what happens), basically it's
exactly the same EXPLAIN output (wrong results of course), but it does add
Using index to the scene_id row (weird).
If I take off the GROUP BY (just to see what happens), basically it's
exactly the same EXPLAIN output (wrong results of course), but it does
remove Using Filesort.
Taking them both off is optimal I suspect, and again, barring the fact the
results are wrong, it takes 1 second for the query. Should I read that to
mean, it is what it is and that's the best I can expect from that
multi-join query? Is there any benefit to splitting this up and if so, how
should I split it?
d
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
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 != '-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=arch...@jab.org
Re: Using where; Using temporary; Using filesort
s 2011/05/27 12:26 -0700, Daevid Vincent [a] the EXTENDED keyword doesn't seem to do anything different? I get the same columns and results??! show warnings 2011/05/27 12:26 -0700, Daevid Vincent 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. Well, in general, of course there are problems for which no heuristic works--and indices _are_ heuristics. Houmuch real time does it take? I have two tables, one of addresses, another of the people living there s names, for the join wherof explain extended shows the same, and it does not bother me because it takes so little time. The output is only 1324 rows long. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Using where; Using temporary; Using filesort
I sent this Friday, but it never made it to the list?! -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, May 27, 2011 12:27 PM To: mysql@lists.mysql.com 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 != '-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=arch...@jab.org
Re: Using where; Using temporary; Using filesort
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:dae...@daevid.com] Sent: Friday, May 27, 2011 12:27 PM To: mysql@lists.mysql.com 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 != '-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=arch...@jab.org
