A question often comes when talking about indexing: should we use multiple column indexes or multiple indexes on single columns? Peter Zaitsev wrote about it back in 2008 and the conclusion then was that a multiple column index is most often the best solution. But with all the recent optimizer improvements, is there anything different with MySQL 5.6?
For this test, we will use these 2 tables (same structure as in Peter’s post):
CREATE TABLE t1000merge ( id int not null auto_increment primary key, i int(11) NOT NULL, j int(11) NOT NULL, val char(10) NOT NULL, KEY i (i), KEY j (j) ) ENGINE=InnoDB; CREATE TABLE t1000idx2 ( id int not null auto_increment primary key, i int(11) NOT[Read more...]