… post is aimed at the optimizer enhancement Multi Range Read (MRR). Its available in both MySQL 5.6 and MariaDB 5.5… the columns that are being fetched do not belong to the secondary index definition (and hence covering index optimization is not used), then primary key… 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 each pk_column value fetched…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… have combined multi-column index on (AGE,STATE). Lets see why it is the case. MySQL indexes are (with few exceptions) BTREE indexes – this index type is… index but not exactly what it gets from the index – typically (unless it is covering index) MySQL gets a “row pointer” which can be primary key…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…, with no delays. Finally, the analysis is usually multi-dimensional. The typical user wants to be …their neighbors will share a set of primary key columns. And not only will every index be a little narrower, the … work by either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and earlier, …
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…column. There will be another blog post about the usage and performance of the splitter. It is multi-threaded(actually multi…. ICE does not have any indexes (not even primary keys). Here is the complete InnoDB … port=3306 socket=/tmp/mysql-inno.sock [mysqld] socket=/tmp/mysql-inno.sock default-storage…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
…PRIMARY KEY (`l_orderkey`,`l_linenumber`), KEY `lineitem_fk2` (`l_suppkey`), KEY `lineitem_fk3` (`l_partkey`,`l_suppkey`), KEY `li_shp_dt_idx` (`l_shipdate`), KEY…MySQL used index to scan rows and then did access to data to read other columns…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… primary key and couple of extra indexes. CREATE TABLE…MySQL 5.1.14-beta sources for MyISAM / InnoDB and MySQL 5.1.14-falcon bitkeeper tree bk://mysql.bkbits.net/mysql…multi-versioning. I think this is a big weakness of Falcon, as ‘using index…key column and ‘pread’ syscall is not used. READ_KEY…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
… even laptops are getting multi cores so we can… with MySQL 
[+] Compact Indexes Compact Indexes are great. Huge indexes is where…columns which are in the index. I think this is big gotcha as having index…Index support Unlike Innodb Falcon does not cluster data by Primary Key or any other key…
Post: Moving from MyISAM to Innodb or XtraDB. Basics
November 22, 2010 By 2 Comments
… backup. Full text search indexes, GIS, multi-column auto increment keys are great examples. …may benefit from different index structure for Innodb, including different primary key setup, as…index scan for Innodb. Defaults You need to know two things about defaults for Innodb. First Depending on MySQL…
