… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M…_custkey 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 1…
Post: Be careful when joining on CONCAT
October 16, 2007 By 10 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 type… rows: 570518 Extra: Using where 2 rows in set (0.00 sec) Then I took a look at MySQL manual and here’s…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… is using filesort as group by execution method, not temporary table as ordinary GROUP BY: mysql> explain select grp, count(*) cnt from dt where slack… rows: 37748736 Extra: Using where; Using temporary; Using filesort 1 row in set (0.01 sec) mysql> explain select grp, count(*) cnt from dt where slack like “a…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…| const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t | ref | k | k | 4 | test.test.i | 1 | Using where; Using index | …ORDER BY col1, col2 DESC MySQL will have to use filesort. Classic for solution …
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 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: s …
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…as network IO or memory to store temporary table, but let us ignore them for … If we’re running Innodb with MySQL we can use Innodb_data_reads , Innodb_data_writes,…0 0 0 0 0 0 # Boolean: # Filesort 0% yes, 99% no # Full scan 0% yes… which are in 100us range are ones where no IO needed to happen so …
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… 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: 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 the…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…: 17217646764 Index_length: 11993816064 mysql> show table status like ‘dim1′\G ***************************…Using where; Using temporary; Using filesort *************************** 2. row *************************** table: fact type: ref key_len: 4 rows: 606 Extra: Using where …
Post: Updated msl (microslow) patch, installation walk-through!
April 20, 2008 By 17 Comments
…. Please do not use the below example as it may not be suitable for your environment. (garfield:~/work/mysql-5.0.51a… to have the patched MySQL installed in the system. Locate where the original mysqld binary is with: (garfield:~/work/mysql-5.0.51a… to get only those which use on-disk temporary storage for intermediate results “tmp_table_on_disk,filesort_on_disk†would be…
