… the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5) optimizer… 100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… looked at the “ORDER BY OPTIMIZATION” section in MySQL manual but could not find a reason why MySQL does not use index in… if there is a rule that MySQL uses to determine whether to use index or filesort? On mysql manual I notice that the above query satisfies all conditions required for MySQL to use an…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
…: MySQL Optimizer correctly knows Innodb tables is clustered by primary key in the sense it would not be faster to do external filesort… ref: NULL rows: 6 Extra: Using filesort 1 row in set (0.00 sec) MySQL Optimizer is also able to figure out every… on (a,id) which means MySQL could skip filesort if ordering is done by primary key: mysql> explain select id from innodb where…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… table | type | rows | Extra | +——-+———–+———–+———————————+ | fact | ALL | 147367284 | Using temporary; Using filesort | | dim1 | eq_ref | 1 | Using where | | dim2 | eq… to provide the missing information. If the MySQL optimizer were right and each of these had …
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… when ORDER BY is done by second table which requires filesort: mysql> explain select test.i from test, test t where test… it is indexed) Force index if needed In some cases MySQL Optimizer may prefer to use different index, which has better selectivity… even if it leads to filesort. The solution for this problem is ether extending your indexes so MySQL Optimizer does not have to…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
…,c2); Using where; Using filesort 1 row in set (0.00 sec) As you can see MySQL 5.1.21 uses sort… interesting is the fact MySQL is unable to handle even basic UNION with limit (without order by) optimally – in creates result set… possible optimizer improvement to do. P.S This post is inspired by Does MySQL Optimize UNION with LIMIT clause topic on our MySQL Forums…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
… second which uses direct column value needs to do the filesort: mysql> explain select * from tst where i=5 and date(d… phase can be skipped all together. Note in this case MySQL Optimizer is rather smart and is able to do this even… functions are different MySQL is not able to do this optimization even in cases when this would be possible: mysql> explain select * from…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… inside MySQL. Why am I looking on reporting performance optimization ? It is for ClickAider project which is growing rapidly and use of MySQL… is – it is using filesort as group by execution method, not temporary table as ordinary GROUP BY: mysql> explain select grp, count… Extra: Using where; Using temporary; Using filesort 1 row in set (0.01 sec) mysql> explain select grp, count(*) cnt from dt…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
…filesort | +—-+————-+——–+——-+—————+——+———+——+——-+—————————–+ 1 row in set (0.00 sec) We can however use UNION to avoid filesort of full table: mysql…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
… a lot of questions at MySQL Performance Forum as well as from our customers regarding query optimization… which had one thing in… it can be slow on large data sets if external filesort is used so make sure to check explain for this… query generation when some query variants fall back to use filesort, and it does not take many of such queries to…
