…: const rows: 10 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from tst where i=5 and date(d… rows: 10 Extra: Using where; Using filesort 1 row in set (0.00 sec) If you take a closer look to WHERE clause you will… key: i key_len: 5 ref: const rows: 10 Extra: Using where; Using filesort 1 row in set (0.00 sec)
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… DISTINCT c from sbtest where id between 501895 and 502895 order by c; or if InnoDB was not used: # Time: 071031 20…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…Data_length: 17217646764 Index_length: 11993816064 mysql> show table status like ‘dim1′\G *************************** 1…: Using where; Using temporary; Using filesort *************************** 2. row *************************** table: fact type: ref key_len: 4 rows: 606 Extra: Using where …
Post: Be careful when joining on CONCAT
October 16, 2007 By 16 Comments
… key: gid key_len: 4 ref: const rows: 53 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: tb1… rows: 570518 Extra: Using where 2 rows in set (0.00 sec) Then I took a look at MySQL manual and here’s…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… MySQL to discard first 10000 records. 1) Results for ENUM: select SQL_NO_CACHE city from cities_enum WHERE state=’…key: NULL key_len: NULL ref: NULL rows: 1439943 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: …
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… 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 different…: user_type key_len: 2 ref: const rows: 32404 Extra: Using where 1 row in set (0.00 sec) Making this change…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… | NULL | 549252 | Using where; Using filesort | … | Using where | +—-+————-+——-+——-+—————+———+———+——+——–+————-+ mysql> select * from table FORCE INDEX(PRIMARY) where…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… become SELECT with appropriate where clause. This however would not tell you full story, especially now as MySQL has triggers which can… head ache. filesort, using temporary are always in the first line So it is impossible to tell at which stage MySQL actually perform… from other tables. No information about using disk for sort or temporary tables MySQL always calls it “filesort” even if it is done…
Comment: What does Using filesort mean in MySQL?
March 9, 2009 By 27 Comments
… read. The answer is clean and clear at the MySQL web site: ========================= Using filesort MySQL must do an extra pass to find out how… match the WHERE clause. The keys then are sorted and the rows are retrieved in sorted order. ========================= http://dev.mysql.com/doc…
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 | …have ORDER BY col1, col2 DESC MySQL will have to use filesort. Classic for solution for …
