…_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=4 join_buffer_size=32M join… | 5 | NULL | 20266 | 100.00 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | lineitem | ref | i_l_suppkey | i_l_suppkey…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…_len: NULL ref: NULL rows: 1439943 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: s type… join for each row to retrieve it. This also means sort can’t be done by index and extra sort pass (filesort) is required, which also makes MySQL to store Join result in temporary table to do…
Post: Be careful when joining on CONCAT
October 16, 2007 By 16 Comments
… case with an awful performance of a rather simple join. It was a join on tb1.vid = CONCAT(‘prefix-’, tb2.id) with… ref: const rows: 53 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: tb1 type…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…: No Full_scan: No Full_join: No Tmp_table: Yes Disk_tmp_table: No # Filesort: Yes Disk_filesort: No Merge_passes: 0 QC…: No Full_scan: No Full_join: No Tmp_table: Yes Disk_tmp_table: No # Filesort: Yes Disk_filesort: No Merge_passes: 0 # InnoDB…: No Full_scan: No Full_join: No Tmp_table: No Disk_tmp_table: No # Filesort: No Disk_filesort: No Merge_passes: 0 # No…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… things like whether Query cache was used or not, if Filesort was used, whether tmp table was created in memory or…: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No # Filesort: No Filesort_on_disk: No Merge_passes… 0 0 0 0 0 0 0 # Boolean: # Filesort 4% yes, 95% no # Filesort on 0% yes, 99% no # Full scan 4…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… | ref | PRIMARY,k | k | 4 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t | ref | k | k | 4 | test.test.i… “const” or “system” access type it is effectively removed from join execution (replaced with constants) and so ORDER BY can be… | ref | PRIMARY,k | k | 4 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t | ref | k | k | 4 | test.test.i…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… it with STRAIGHT_JOIN: explain select STRAIGHT_JOIN …. +——-+———–+———–+———————————+ | table | type | rows | Extra | +——-+———–+———–+———————————+ | fact | ALL | 147367284 | Using temporary; Using filesort | | dim1 | eq…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
….0 with addition of greedy join the problem on looking at too many table join combinations in joins with very large tables is… catches possibly complex queries it can give you head ache. filesort, using temporary are always in the first line So it… disk for sort or temporary tables MySQL always calls it “filesort” even if it is done in memory. Also it would…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… PRIMARY NULL NULL NULL 25 100.00 Using temporary; Using filesort 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c… 100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY… lookups as well. But this works only with joins and specifically with Block Access Join Algorithms. So I am not going to…
Post: Finding what Created_tmp_disk_tables with log_slow_filter
September 22, 2008 By 3 Comments
…-separated string. [qc_miss, full_scan, full_join, tmp_table, tmp_table_on_disk, filesort, filesort_on_disk] So now all I had to do was set the filter to “tmp_table_on_disk,filesort_on…. SET GLOBAL log_slow_filter:= “tmp_table_on_disk,filesort_on_diskâ€; Wait a couple of minutes and then set…
