… of advanced join methods at this point (the work is on a way) – MySQL can’t do hash join or sort merge join – it… to work with in temporary table etc. Prefer full table scans to index accesses – For large data sets full table scans are often 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: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… base table. Knowing this, the query result set is treated as a materialized view over a union of all the already joined and aggregated data from all the nodes. A single temporary table is used to store the… of compute resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies to the…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…=’Contains all avaialble data from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_fact’G *************************** 1. row… in memory. This frees disk to use on-disk temporary storage for hash joins and other background operations. This will have a…
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…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… including Percona Xtrabackup, MyLVMBackup and others use FLUSH TABLES WITH READ LOCK to temporary make MySQL read only. In many cases the period…) As you can see FLUSH TABLES WITH READ LOCK is waiting for that very nasty “full join” select to complete. What is… no MYISAM like table lock priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql> show processlist…
Post: Upgrading MySQL
January 5, 2010 By 24 Comments
…is change of TIMESTAMP output format in MySQL 4.1 or changing JOIN evaluation in 5.0 (which could both…made it to caught up you can use mk-table-checksum to ensure data is the same. It …temporary get extra boxes for time of upgrade. Swapping master. Finally as you have your slave running on new MySQL…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… MySQL 5.5 but takes slightly more time as compared to BKA algorithm of MySQL 5.6. Test Case C – Join a small table with a large table with a WHERE clause on…_orderdate | PRIMARY | 4 | NULL | 2993459 | 50.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | l | ref | PRIMARY,i_l_orderkey…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 3 Comments
… I’m giving a talk on it at the MySQL 2011 CE, and I figured I should blog …are stored in a table. Storing the results of a SQL statement into a table (even a temporary table) is called …as much space (similar to ALTER TABLE) Supports all SQL syntax (like outer join) but can’t be refreshed …
Comment: MySQL VIEW as performance troublemaker
February 16, 2013 By 57 Comments
…Expanded as a macro or creating temporary tables” When would it benefit a view to create a temporary tables over expanding as a macro… to run as a macro then to use the unindexed temporary table. Truly I thought (albit incorrectly) that views were equal or… my surprise MySQL made it quite evident this is not that case. In my situation I had 5 tables left outer joining. (about…
Post: Be careful when joining on CONCAT
October 16, 2007 By 16 Comments
…rows analyzed was really huge: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘…Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: tb1 type: ALL possible_…
