… | | 6950 | 290 | +——+———+ 11 rows in set (29.68 sec) Use of extra temporary table for buffering helps us to get result set… it. The thing is – it is using filesort as group by execution method, not temporary table as ordinary GROUP BY: mysql> explain…: NULL key_len: NULL ref: NULL rows: 37748736 Extra: Using where; Using temporary; Using filesort 1 row in set (0.01 sec) mysql> explain…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
Post: What does Using filesort mean in MySQL?
March 5, 2009 By 27 Comments
… answer is something like “rows are being placed into a temporary table which is too big to fit in memory, so… not the same thing. First of all, this is Using temporary. Secondly, temporary tables may go to disk if they are too big… big” means, or I might ask you the other reason temporary tables go to disk!) The truth is, filesort is badly…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… possibly complex queries it can give you head ache. filesort, using temporary are always in the first line So it is impossible… creates temporary table and so how much rows will be stored to temporary table. Sometimes it is enough to perform sorting or use temporary… reading data from other tables. No information about using disk for sort or temporary tables MySQL always calls it “filesort” even if…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… a temporary table without updating indexes took 18 seconds, and about the same time was spent on rebuilding the index using fast… to copy the data to a temporary table is constant. The reason is that when using merge sort, InnoDB has to scan… index creation uses temporary files in tmpdir for all indexes being created. So make sure you have enough tmpdir space when using expand…
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… not have “using temporary” in EXPLAIN) this was not issue for given case as GROUP BY in question required temporary table plus it… creating temporary table though which can be quite high. Sharing connection no more works If you use buffered query you can use same… INSERT … SELECT to the temporary table with auto_increment column and fetch data from this table using auto increment column ranges instead…
Post: UNION vs UNION ALL Performance
October 5, 2007 By 15 Comments
… need to use temporary table to store result set, however this is not correct – both UNION ALL and UNION distinct use temporary table for… into. Interesting enough the fact UNION and UNION ALL require temporary table can only be seen in SHOW STATUS – EXPLAIN does… in execution speed comes from the fact UNION requires internal temporary table with index (to skip duplicate rows) while UNION ALL…
Post: Derived Tables and Views Performance
August 31, 2006 By 16 Comments
… Tables are still handled by materializing them in the temporary table, furthermore temporary table with no indexes (so you really do not… even separate temporary table will be faster as you can add proper indexes to the table in this case. Consider using temporary views instead of derived tables If you really need to use subselect in from clause consider creating view using it in…
Comment: Performance impact of complex queries
January 19, 2007 By 11 Comments
… was fragmented etc. “use temporary or better use HEAP type” This is wrong way to put it. You can create temporary table which can be any storage engine including HEAP or MySQL will automatically create temporary table for you… is true ensuring your temporary table is not converted to the MyISAM is good way to improve performance. “use mysql RAID ability…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… | Using index; Using temporary; Using filesort | | 1 | SIMPLE | lineitem | ref | i_l_suppkey | i_l_suppkey | 5 | dbt3.supplier.s_suppkey | 250 | 100.00 | Using…_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: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 3 Comments
… results of a SQL statement into a table (even a temporary table) is called materializing the results. Views are similar to… other conditions must be fully computed and stored in a temporary table before they can be accessed, which is very expensive… out what changed in the database Delta computation – How it uses those changes to compute the differences between the old results…
