… you compare the use of subqueries/multiple joins vs. multiple queries (e.g. temp tables)? For performance, it’s hard to make… are other drawbacks to using temporary tables in this way, for example when using replication, a temporary table could vanish if the slave restarts… more rows. But by avoiding the temporary table, and making lookups to the `kind_types` table by primary key, the result was a…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…_orderdate | PRIMARY | 4 | NULL | 2993459 | 50.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | l | ref | PRIMARY,i_l_orderkey… are joining tables with no indexes on the join condition (Full Join). The best performance with Hash Join can be achieved when the left table can…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…: NULL rows: 1439943 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: s type: eq_ref possible_keys… (filesort) is required, which also makes MySQL to store Join result in temporary table to do the sort, all together makes things quite…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
…. Or perhaps we have some other tables with ASCII identifiers and we’re doing JOINs against tables in this database and seeing that…, you can’t recreate the “dos2″ table, and you can’t turn the orphaned temporary table back into its former self unless you… this database to get the actual filename of the temporary table rather than the temporary table name that was displayed in the error message…
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 want to join two derived tables for example). One…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… to work with in temporary table etc. Prefer full table scans to index accesses – For large data sets full table scans are often faster… 1% or rows or less full table scan may be faster. Avoid joins to large tables Joining of large data sets using nested loops is very expensive. Try to avoid it. Joins to smaller tables is…
Post: Finding what Created_tmp_disk_tables with log_slow_filter
September 22, 2008 By 3 Comments
… temporary tables being created on disk. show global status like ‘Created_tmp%’ | Created_tmp_disk_tables | 91970 | | Created_tmp_files | 19624 | | Created_tmp_tables… a comma-separated string. [qc_miss, full_scan, full_join, tmp_table, tmp_table_on_disk, filesort, filesort_on_disk] So now all…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… is simply expanded as a macro or Temporary Table in which case VIEW is materialized to temporary tables (without indexes !) which is later used… used for temporary table creation from the outer query and plus if you use more then one Temporary Tables views which you join together you may have serious issues because such tables do not get any…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… with addition of greedy join the problem on looking at too many table join combinations in joins with very large tables is elevated, however there… creates temporary table and so how much rows will be stored to temporary table. Sometimes it is enough to perform sorting or use temporary table with…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… example it may use indexes or do a full table scan, or a temporary table may be needed. These are the things that… from a table. Full_join means any of the joins didn’t use indexes. If a temporary table is required for query execution Tmp_table will be set. Sometimes that table must…
