… secs. I dont understand why MySQL uses index merge after a couple of mins when previously it was using a single index. I… | SIMPLE | ProjectTbl2 | const | PRIMARY,namespace_ix | PRIMARY | 4 | const | 1 | Using filesort | | 1 | SIMPLE | LinksTbl2 | index_merge | tstamp,userid,converted_from_id…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M… 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…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… 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 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…
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… performed from an index, it’s a filesort. It has nothing to do with files. Filesort should be called “sort.” It is…. I refer you to Sergey Petrunia’s article on How MySQL executes ORDER BY. You can also read about it in…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
…: NULL ref: NULL rows: 6 Extra: Using filesort 1 row in set (0.00 sec) MySQL Optimizer is also able to figure out… on (a,id) which means MySQL could skip filesort if ordering is done by primary key: mysql> explain select id from innodb where… 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
… | 37915 | Using where; Using filesort | +—-+————-+——–+——-+—————+——+———+——+——-+—————————–+ 1 row in set (0.00 sec) We can however use UNION to avoid filesort of full table: mysql> explain (select… | 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: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… thing is – it is using filesort as group by execution method, not temporary table as ordinary GROUP BY: mysql> explain select grp, count… ref: NULL rows: 37748736 Extra: Using where; Using temporary; Using filesort 1 row in set (0.01 sec) mysql> explain select grp, count(*) cnt from… 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
… ref: const,const rows: 296338 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> explain select * from goods force index… million of rows to analyze but we got rid of filesort so MySQL can stop as soon as 10 rows are sent… for given seller_id (as it well can be skewed) using filesort is better as otherwise very large portion of index may…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
… to avoid order by while second which uses direct column value needs to do the filesort: mysql> explain select * from tst where i…: i key_len: 5 ref: const rows: 10 Extra: Using where; Using filesort 1 row in set (0.00 sec) If you take… key: i key_len: 5 ref: const rows: 10 Extra: Using where; Using filesort 1 row in set (0.00 sec)
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…length: 17217646764 Index_length: 11993816064 mysql> show table status like ‘dim1′\G *************************** … | fact | ALL | 147367284 | Using temporary; Using filesort | | dim1 | eq_ref | 1 | Using where | | dim2 | eq_ref | 1 | Using where …
