… with columns that had single-column indexes on them, MySQL could sometimes make use of the multiple indexes. For instance, “SELECT foo… Extra: Using intersect(user_type,status,parent_id); Using where; Using index; Using filesort At first glance, this might not look too bad. MySQL is using three…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… it covers apply to other products besides MySQL. The logical principles still hold true: using an index really means leveraging a pre…, but the new version promises some exciting new ways that MySQL can use indexes, such as: Index merge improvements. But I expect… it as a separate step. This is when we see “Using temporary; Using filesort” in the EXPLAIN report. Hopefully, the size of this…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… by Cary Millsap. So how we can use this data to estimate capacity of MySQL system ? We can look at CPU and… provide our estimates. If we’re running Innodb with MySQL we can use Innodb_data_reads , Innodb_data_writes, Innodb_os_log… lock wai 0 0 0 0 0 0 0 # Boolean: # Filesort 0% yes, 99% no # Full scan 0% yes, 99% no…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… Disk_tmp_table will state that. Filesort is for indicating that filesort algorithm was used, while Disk_filesort means that sorting was done through temporary files. INNODB USAGE The final part are the InnoDB usage statistics. MySQL…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… (which includes things like whether Query cache was used or not, if Filesort was used, whether tmp table was created in memory or… in the overview section. So say if you were using the vanilla MySQL server, you would see an entry like this in… that you can use to gather more data about the underlying tables involved and the query execution plan used by MySQL. The end…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 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… if you would have ORDER BY col1, col2 DESC MySQL will have to use filesort. Classic for solution for this would be to…
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: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…variants of MySQL are listed below. Configuration Let’s first take a look at the configuration used with different MySQL flavors. MySQL 5….nationkey | i_s_nationkey | 5 | const | 808 | 100.00 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | lineitem | ref | i_l_suppkey | i…
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 sort… 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…
Comment: What does Using filesort mean in MySQL?
March 6, 2009 By 27 Comments
… you expect because there are cases when “you always get filesort with ORDER BY”. There’s example below without the table…: NULL ref: NULL rows: 3 Extra: Using filesort 1 row in set (0.03 sec) mysql> ALTER TABLE t1 ADD INDEX (id); Query… key: NULL key_len: NULL ref: NULL rows: 3 Extra: Using filesort 1 row in set (0.00 sec)
