… nation ALL PRIMARY NULL NULL NULL 25 100.00 Using temporary; Using filesort 1 SIMPLE customer ref PRIMARY,i_c_nationkey i… 232722 100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…_JOIN …. +——-+———–+———–+———————————+ | table | type | rows | Extra | +——-+———–+———–+———————————+ | fact | ALL | 147367284 | Using temporary; Using filesort | | dim1 | eq_ref | 1 | Using where | | dim2 | eq_ref | 1…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…: NULL key_len: NULL ref: NULL rows: 1439943 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: s… and extra sort pass (filesort) is required, which also makes MySQL to store Join result in temporary table to do the sort…
Post: Be careful when joining on CONCAT
October 16, 2007 By 16 Comments
…_len: 4 ref: const rows: 53 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: tb1…_len: 4 ref: const rows: 53 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: tb1…
Comment: How much overhead is caused by on disk temporary tables
August 17, 2007 By 26 Comments
…. MySQL has 2 ways of doing group by via filesort and with temporary table. filesort returns result in sorted order just by nature… Monty decided to sort results by default even in case temporary table was used. May be this was his interpretation of…
Comment: MySQL: Followup on UNION for query optimization, Query profiling
December 15, 2006 By 14 Comments
… regarding the filesort – in both cases (when using “IN (18,19,20)” and when using “UNION”) mysql makes a temporary table of about the same size on which it does a filesort. Basically, in the first version the results are filtered by age and then a temporary table is created…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… the end. The reason for slowness was of course huge temporary table was required (there were about 5 million of distinct pages visited during that day) which resulted in on disk temporary table which as we know quite slow. Of course it… be possible to allocate more memory to the temporary table or switch to filesort method and get result faster. I however picked…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… you’re mentioning allowing group by to be performed without temporary table or external sort. Q: How can we manage indexes… you have Index on the column MySQL can avoid temporary table or filesort for group by by this column. This works because…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… 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
… other resources such as network IO or memory to store temporary table, but let us ignore them for a moment. The… lock wai 0 0 0 0 0 0 0 # Boolean: # Filesort 0% yes, 99% no # Full scan 0% yes, 99% no…
