tag:blogger.com,1999:blog-5553694220766703655.post3357088574885122722..comments2013-02-28T01:20:10.249-08:00Comments on didrik @ MySQL: Optimizing MySQL filesort with small limit.didrikhttp://www.blogger.com/profile/05846571303485695354noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-5553694220766703655.post-88038172201760674092013-02-28T01:20:10.249-08:002013-02-28T01:20:10.249-08:00If there is no 'using filesort' in the exp...If there is no 'using filesort' in the explain, then we're reading<br />the table in sorted order anyways, and will stop sending more results<br />when we have reached the limit.<br /><br />By the way, there's no 'using priority queue' in the explain, but the<br />optimizer trace will show it, see here http://jorgenloland.blogspot.co.uk/2011/10/optimizer-tracing-query-execution-plan.htmldidrikhttps://www.blogger.com/profile/05846571303485695354noreply@blogger.comtag:blogger.com,1999:blog-5553694220766703655.post-45509470370520123492013-01-22T12:01:02.072-08:002013-01-22T12:01:02.072-08:00Wouldn't EXPLAIN actually show this? If there&...Wouldn't EXPLAIN actually show this? If there's an extra pass-through for sorting, Extra: Using filesort would show up. So wouldn't a lack of that be an indication that this is happening? For example, comparing EXPLAIN on MySQL 5.5 and 5.6, 5.5 would have Extra: Using filesort and 5.6 wouldn't?<br /><br />Or am I understanding the "filesort" thing wrong?Sheeri K. Cabralhttps://www.blogger.com/profile/13990877688502800403noreply@blogger.comtag:blogger.com,1999:blog-5553694220766703655.post-49141484871556163102012-02-01T11:14:28.907-08:002012-02-01T11:14:28.907-08:00Didrik, what about queries that don't need a t...Didrik, what about queries that don't need a table scan for example suppose you add an index on the f1 column to the t1 table in your example, and then execute the query:<br /><br />SELECT * from t1 WHERE f1 between x AND y ORDER BY f2 LIMIT 100;Ovais Tariqhttps://www.blogger.com/profile/04778564768448950777noreply@blogger.comtag:blogger.com,1999:blog-5553694220766703655.post-36493444631399683172011-04-29T00:04:22.209-07:002011-04-29T00:04:22.209-07:00Oh, I forgot about SQL_CALC_FOUND_ROWS. This query...Oh, I forgot about SQL_CALC_FOUND_ROWS. This query:<br /><br />SELECT SQL_CALC_FOUND_ROWS * FROM t1 ORDER BY f2 LIMIT 100;<br /><br />still takes about 10 seconds with MySQL 5.6. Older versions will do the full sort/merge. Executing this with MySQL 5.5 takes about half an hour on my desktop. See bug: http://bugs.mysql.com/bug.php?id=18454didrikhttps://www.blogger.com/profile/05846571303485695354noreply@blogger.com