I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in… cannot be used for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table participating in…in the join condition and then a hash table lookup is performed, on the hash table we created earlier. This step is known as…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…created which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…you a simple example. Suppose you have the following query executed on the InnoDB table: SELECT non_key_column… mrr_buffer_size=4M Created_tmp_disk_tables 1 1 1 1 1 Created_tmp_tables 1 1 1 1…in MariaDB 5.5 and not in MySQL 5.6, is that because of a bug in MySQL 5.6 code? As MRR was used in both MySQL…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…example: SESSION1: mysql> show tables; +——————–+ | Tables_in_dumptest | +——————–+ | A | | B | | C | +——————–+ 3 rows in set (0.00 sec) mysql> select count(*) from A; +———-+ | count(*) | +———-+ | 2359296 | +———-+ 1 row in…
Comment: InnoDB's gap locks
April 13, 2012 By 14 Comments
… example: “InnoDB provides REPEATABLE READ for read-only SELECT, but it behaves as if you use READ COMMITTED for all write queries, in… Table: t Create Table: CREATE TABLE `t` ( `i` int(11) DEFAULT NULL, `c` char(20) DEFAULT NULL, KEY `i` (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql> select…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…modified the table‘s rows. For example: transaction1> START TRANSACTION; transaction1> SELECT * FROM…there is no need to create locks to prevent that from… (non repeatable reads) so in order to prevent problems…mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… a quick example of the list of tables used by the above view, as stored in the data dictionary: mysql> select * from flexviews.mview_table where mview… 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 COMPLETE…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…example, we have a parent table Foo and a child table Bar: CREATE TABLE Foo (A INT, B INT, PRIMARY KEY (A, B)); CREATE TABLE… orphaned rows in Bar: SELECT Bar.ID …mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN_NAME, ‘ AS `’, P.TABLE_SCHEMA, ‘.’, P.TABLE…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
… words table and I create words2. This is the data upon which we will operate: create table words2 partition by hash(bucket) partitions 12 as select… set operations work, I’ll operate in only one dimension for the first example, just like the MySQL client. This will be a… to factor numbers create table dataset ( id bigint auto_increment primary key, prime_number bigint ); spread this over as many machines as necessary using…
