… can cause downward spikes in performance. Other then that…NOT NULL, PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` …all now there are pros and cons to using secondary indexes. While secondary indexes…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… the secondary keys is simply because you are writing 4 times fewer B-tree values. The primary key and each secondary key each incur… only the primary key because the entire table fits in main memory. Each row is on the order of 30 bytes in the primary table… buffer pool, we would expect the primary table to simply fit. But with all 4 indexes the data itself is 2 or 3…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… the secondary index definition (and hence covering index optimization is not used), then primary key lookups have to be performed for each secondary key entry fetched. This means that secondary key… be reinitialized, because the buffer was small and not all index tuples could fit in the buffer. If this is > 0 then it…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
…. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both… aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
…use index in the following …PRIMARY KEY (id), KEY (a), KEY (b) ) ENGINE=InnoDB Table 1 has 512000 randomly generated rows. This query uses index (type: index, key: a, key…index or filesort? On mysql manual I notice that the above query satisfies all conditions required for MySQL to use an index…
Comment: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
April 7, 2012 By 12 Comments
… columns l_shipmode and l_shipinstruct. And so all rows matching condition l_partkey = x and and …Primary Key and returned to MySQL server which will then in turn apply the remaining parts of the WHERE clause l_shipmode in…i found the optimization only use the prefix of index, but not the whole index…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
…primary key in the sense it would not be faster to do external filesort than to do lookups in primary key…key also holds primary key value so primary key value can be read from index, making some queries index covered which previously was not: Notice “Using Index…
Comment: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
…’ve been doing all of this stuff for years… or so I thought. 
Buried in there, you say ‘having primary key in all indexes‘. Can you elaborate…`), KEY `Email` (`Email`, `UserID`) ) ENGINE=InnoDB; ? If so, it looks like I (wrongly?) assumed that the Primary Key was always referenced by other indexes…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
…=READ-COMMITTED. This option has some performance benefits, especially in locking in 5.0 and even more to come with MySQL 5… by primary key, having primary key in all indexes (so keep primary key short), fast lookups by primary keys (try to use it in joins), large unpacked indexes (try to be easy on indexes…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… number of benefits – caching data and index in memory -so cached lookups are very fast, clustering by primary key – so for disk bound lookups… indexes are defined. The primary key itself does not get much larger whatever columns you place into it, as primary key BTREE contains all table data in…
