Optimizing your MySQL Application

Disadvantages of Indexing

So far, I’ve only discussed why indexes are great. However, they do have several disadvantages.

First, they take up disk space. Usually this isn’t significant, but if you decided to index every column in every possible combination, your index file would grow much more quickly than the data file. If you have a large table, the index file could reach your operating system’s maximum file size.

Second, they slow down the speed of writing queries, such as DELETE, UPDATE, and INSERT. This is because not only does MySQL have to write to the data file, it has to write everything to the index file as well. However, you may be able to write your queries in such a way that the performance degradation is not very noticeable.

Conclusion

Indexes are one of the keys to speed in large databases. No matter how simple your table, a 500,000-row table scan will never be fast. If you have a site with a 500,000-row table, you should really spend time analyzing possible indexes and possibly consider rewriting queries to optimize your application.

As always, there is more to indexing than I covered in this article. More information can be found in the official MySQL manual, or in Paul DuBois’ great book, MySQL.

Query Reference

Adding a “normal” index via CREATE INDEX:
CREATE INDEX [index_name] ON tablename (index_columns); Example: CREATE INDEX fname_lname_age ON people (firstname,lastname,age);

Adding a unique index via CREATE INDEX:
CREATE UNIQUE INDEX [index_name] ON tablename (index_columns); Example: CREATE UNIQUE INDEX fname_lname_age ON people (firstname,lastname,age);

Adding a “normal” index via ALTER TABLE:
ALTER TABLE tablename ADD INDEX [index_name] (index_columns); Example: ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);

Adding a unique index via ALTER TABLE:
ALTER TABLE tablename ADD UNIQUE [index_name] (index_columns); Example: ALTER TABLE people ADD UNIQUE fname_lname_age (firstname,lastname,age);

Adding a primary key via ALTER TABLE:
ALTER TABLE tablename ADD PRIMARY KEY (index_columns); Example: ALTER TABLE people ADD PRIMARY KEY (peopleid);

Adding a “normal” index via CREATE TABLE:
CREATE TABLE tablename (    
 rest of columns,    
 INDEX [index_name] (index_columns)    
 [other indexes]    
); Example:
CREATE TABLE people (    
 peopleid SMALLINT UNSIGNED NOT NULL,    
 firstname CHAR(50) NOT NULL,    
 lastname CHAR(50) NOT NULL,    
 age SMALLINT NOT NULL,    
 townid SMALLINT NOT NULL,    
 INDEX fname_lname_age (firstname,lastname,age)    
);

Adding a unique index via CREATE TABLE:
CREATE TABLE tablename (    
 rest of columns,    
 UNIQUE [index_name] (index_columns)    
 [other indexes]    
); Example:
CREATE TABLE people (    
 peopleid SMALLINT UNSIGNED NOT NULL,    
 firstname CHAR(50) NOT NULL,    
 lastname CHAR(50) NOT NULL,    
 age SMALLINT NOT NULL,    
 townid SMALLINT NOT NULL,    
 UNIQUE fname_lname_age (firstname,lastname,age)    
);

Adding a primary key via CREATE TABLE:
CREATE TABLE tablename (    
 rest of columns,    
 INDEX [index_name] (index_columns)    
 [other indexes]    
); Example:
CREATE TABLE people (    
 peopleid SMALLINT NOT NULL AUTO_INCREMENT,    
 firstname CHAR(50) NOT NULL,    
 lastname CHAR(50) NOT NULL,    
 age SMALLINT NOT NULL,    
 townid SMALLINT NOT NULL,    
 PRIMARY KEY (peopleid)    
);

Dropping (removing) a “normal” or unique index via ALTER TABLE:
ALTER TABLE tablename DROP INDEX index_name; Example: ALTER TABLE people DROP INDEX fname_lname_age;

Dropping (removing) a primary key via ALTER TABLE:
ALTER TABLE tablename DROP PRIMARY KEY; Example: ALTER TABLE people DROP PRIMARY KEY;