Showing entries 1 to 1
Displaying posts with tag: tuples (reset)
Duplicate indexes and tuples

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