…worse case scenario by picking the random set of primary key values from sysbench table and reading them over …needed for page decompression) if this is the policy innodb would have chosen. In reality however results are … and 16K Compressed to be very similar on MySQL 5.5 As a summary: If you have …
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… workings of one of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we…, `role_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `role_id_note` (`role_id`,`note`) ) ENGINE=InnoDB AUTO_INCREMENT=22187769 DEFAULT CHARSET=utf8…-party, independent and technical opinion about the new features of MySQL query planner? Are you not yet familiar with terms like…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
… is appealing to improve performance for MySQL Applications ? Make it Lockless Can we re-implement MySQL Query Cache so it is lockless… can use same “cheat” as MySQL 5.5 uses for Innodb Buffer Pool and have multiple MySQL Query Cache instances hashed by query… of applications. Many applications in MySQL have very large number of queries doing updates through primary key and use primary key for selects. I believe…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… ignore this column. It is used to prevent wide innodb primary keys on the MV. mysql> select mview$pk as rank, customer_id, total…) | +——————+ | 155187034 | <-- too high +------------------+ 1 row in set (0.68 sec) mysql> select sum(total_lines) from dashboard_customer_sales ; +——————+ | sum(total…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… MRR: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) For… following: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… defaults between MySQL 5.5 and MySQL 5.6 and innodb_old_blocks_time immediately came to mind. The InnoDB plugin introduced innodb_old_blocks…), key(P_Name), key(P_MFGR), key(P_Category), key(P_Brand) ); DROP TABLE IF EXISTS supplier; CREATE TABLE supplier ( S_SuppKey int primary key, S…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
… NULL, PRIMARY KEY (`tracker_id`,`username`,`time_id`), KEY `block_id` (`block_id`) ) ENGINE=InnoDB …mysql> SHOW STATUS LIKE ‘Innodb_scan_pages%’; +——————————+——-+ | Variable_name | Value | +——————————+——-+ | Innodb_scan_pages_contiguous | 45 | | Innodb…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… with different typical data access patterns: primary key single row lookup, primary key range lookup, same access types for primary key and full table scans. To… MySQL version We used MySQL 5.1.14-beta sources for MyISAM / InnoDB and MySQL 5.1.14-falcon bitkeeper tree bk://mysql.bkbits.net/mysql… query with access by primary key. InnoDB is faster than MyISAM by 6-9%. Falcon shows very bad scalabilty. READ_KEY_POINT Query: SELECT…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
…, `b` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> select count(*) from idxitest where…(11) NOT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `a_2` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> select * from idxitest…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… Choose primary keys very carefully, especially with InnoDB tables — don’t use GUIDs. Which brings me to my next point: Use InnoDB Assuming that you will use the stock MySQL server, InnoDB is usually your best bet. (Actually… capacity. What does this have to do with InnoDB? Data clustering. InnoDB‘s primary keys define the physical order rows are stored in…
