… | SIMPLE | ProjectTbl2 | const | PRIMARY,namespace_ix | PRIMARY | 4 | const | 1 | Using filesort | | 1 | SIMPLE | LinksTbl2 | index_merge | tstamp,userid,converted_from_id… | prjid_ix,item_desc | 4,1 | NULL | 294 | Using intersect(prjid_ix,item_desc); Using where | | 1 | SIMPLE | PersonTbl2 | eq_ref | PRIMARY…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… * FROM table1 ORDER BY a LIMIT 1698, 1 This one uses filesort (Extra: Using filesort): EXPLAIN SELECT * FROM table1 ORDER BY a LIMIT 1699, 1 Can someone EXPLAIN if there is a rule that MySQL uses…, you can check whether MySQL can use indexes to resolve the query. It cannot if you see Using filesort in the Extra column.
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… SIMPLE nation ALL PRIMARY NULL NULL NULL 25 100.00 Using temporary; Using filesort 1 SIMPLE customer ref PRIMARY,i_c_nationkey i… i_o_orderdate 4 NULL 232722 100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 100.00 Using where…
Post: What does Using filesort mean in MySQL?
March 5, 2009 By 27 Comments
… to work at Percona, and I asked you “what does Using filesort mean in EXPLAIN,” what would you say? I have asked… truth is, filesort is badly named. Anytime a sort can’t be performed from an index, it’s a filesort. It has nothing to do with files. Filesort should be called “sort.” It is…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… does not work with it. The thing is – it is using filesort as group by execution method, not temporary table as ordinary…: NULL key_len: NULL ref: NULL rows: 37748736 Extra: Using where; Using temporary; Using filesort 1 row in set (0.01 sec) mysql> explain… Extra: Using where; Using filesort 1 row in set (0.00 sec) As I forced FileSort execution method for GROUP BY by using SQL_BIG…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
… key_len: 8 ref: const,const rows: 296338 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> explain select… million of rows to analyze but we got rid of filesort so MySQL can stop as soon as 10 rows are… for given seller_id (as it well can be skewed) using filesort is better as otherwise very large portion of index may…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… | 4 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t | ref | k | k | 4 | test.test.i | 1 | Using where; Using index | +—-+————-+——-+——+—————+——+———+————-+——+———————————+ 2 rows in… | 4 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t | ref | k | k | 4 | test.test.i | 1 | Using where; Using index | +—-+————-+——-+——+—————+——+———+————-+——+———————————+ 2 rows in…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
… key: NULL key_len: NULL ref: NULL rows: 6 Extra: Using filesort 1 row in set (0.00 sec) MySQL Optimizer is… effectively key on (a,id) which means MySQL could skip filesort if ordering is done by primary key: mysql> explain select… ref: const rows: 1 Extra: Using where; Using index; Using filesort 1 row in set (0.00 sec) Filesort should be avoided in this case…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
… | 1 | NULL | 37915 | Using where; Using filesort | +—-+————-+——–+——-+—————+——+———+——+——-+—————————–+ 1 row in set (0.00 sec) We can however use UNION to avoid filesort of full table… | Using where | | 3 | UNION | people | ref | age | age | 1 | const | 12631 | Using where | |NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
…: Using sort_union(c1,c2); Using where; Using filesort 1 row in set (0.00 sec) As you can see MySQL 5.1.21 uses… key: NULL key_len: NULL ref: NULL rows: NULL Extra: Using filesort 3 rows in set (0.00 sec) The query looks… key: NULL key_len: NULL ref: NULL rows: NULL Extra: Using filesort 3 rows in set (0.00 sec) As you can…
