… extended to improve join performance. As I told you above, when table t1 would be joined to table t2, then selected rows from t1 would… table lookup is performed, on the hash table we created earlier. This step is known as probe step. This join algorithm works best for highly selective…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which… filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode`,`l_shipinstruct`) and the WHERE condition defined as: l_partkey = x and l_quantity >= 1 and l_quantity…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…TABLE. When ALTER TABLE is Performed in many cases it will Create temporary table with modified structure, copy data to that table and when drop original table…> select * from C; +——+—+ | t | i | +——+—+ | test | 0 | +——+—+ 1 row in set (0.00 sec) As you can see as we altered table…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…range primary key lookup as follows: SELECT non_key_column from tbl WHERE pk_column IN (…) As you can see by…w/ mrr_buffer_size=4M Created_tmp_disk_tables 1 1 1 1 1 Created_tmp_tables 1 1 1 1 1…refills + 1 MRR range scans had to be performed. As in the table above you can with default buffer size of…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
… as …select count(*) from salaries where emp_no = 10001; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set (0.00 sec) mysql> show tables; +———————+ | Tables…table back. Because leaving a production server with active snapshots can affect performance…` to create …
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…the table‘s rows. For example: transaction1> START TRANSACTION; transaction1> SELECT * FROM t…affecting the concurrency and the performance you can disable them in two… there is no need to create locks to prevent that from happening… Conclusion MySQL uses REPEATABLE READ as the default isolation level …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… to create incrementally refreshable views from SQL statements. It reads one or more “CREATE TABLE db.schema … AS SELECT” and/or “INSERT INTO db.schema … AS SELECT… is refreshed. This is not much different from building a table with CREATE TABLE .. AS SELECT. In fact, this is actually part of what the… method performance comparison For demonstration purposes, I did the following: Created one view of each type. The view is the same as the…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 2 Comments
… makes this perfectly clear: CREATE TABLE .. AS SELECT (CTAS). The results of the SELECT portion of the statement are stored in a table. Storing the results… write activity. Materialized views can be used to enhance performance by acting as a cache. Further, the cost of a cache miss…
Post: Derived Tables and Views Performance
August 31, 2006 By 14 Comments
… each other but how do they compare in terms of performance ? Derived Tables in MySQL 5.0 seems to have different implementation… mean in terms of performance: Query on base table executes using index and it is very fast mysql> select * from test where i… is fast again: mysql> create view v as select * from test; Query OK, 0 rows affected (0.08 sec) mysql> select * from v where…
Comment: Can MySQL temporary tables be made safe for statement-based replication?
May 27, 2008 By 8 Comments
… that you basically need to perform some kind of action to initiate the transaction before creating your temp table. It seems that merely issuing a “START TRANSACTION” is not enough. For example, if you create a table with 1 column: CREATE TABLE… do with it… that a simple “CREATE TABLE” does not cause a transaction to start, but that “CREATE TABLE AS SELECT” does. I logged a bug…
