At my current job, I have seen much over indexing in some of our
production schemas.
Here is a quick rule of thumb; if you have an schema such
as
CREATE TABLE blah ( col1 int default not null, col2 int default
not null, primary key (col1,col2), key col1, key col2);
or
CREATE TABLE blah ( col1 int default not null, col2 int default
not null,unique key (col1,col2), key col1, keycol2);
The index ‘key col1’ is redundant to the primary (or unique key)
as col1 is the first in the other indexes tuple. Creating the
col1 index will do nothing more than cause a big performance hit
for updates and reduce the valuable useful space of the innodb
buffer pool (if innodb is being used) intern making things slow.
Showing entries 1 to 1
Feb
21
2010
Showing entries 1 to 1