Indexes can reduce the amount of data your query touches by orders of magnitude. This results in a proportional query speedup. So what happens when you define a nice set of indexes and you don’t get the performance pop you were expecting? Consider the following example:
mysql> show create table t; | t | CREATE TABLE `t` ( `a` varchar(255) DEFAULT NULL, `b` bigint(20) NOT NULL DEFAULT '0', `c` bigint(20) NOT NULL DEFAULT '0', `d` bigint(20) DEFAULT NULL, `e` char(255) DEFAULT NULL, PRIMARY KEY (`b`,`c`), KEY `a` (`a`,`b`,`d`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
Now we’d like to perform the following query:
select sql_no_cache count(d) from t where a = 'this is a test' and b between 8000000 and 8100000;
Great! We have index a, which cover this query. Using a should be really fast. You’d expect to use the index to jump to the beginning of the ‘this is a test’ values for …
[Read more]