For the last couple of weeks I’ve been talking about indexes and how they help speed up database searches. I talked about their pros and cons and then talked about the different types of indexes and when each is appropriate.
There’s one thing I haven’t shown you yet in this series about MySQL, and that’s how you actually create indexes and add them to existing database tables. That’s what I want to talk about today. I’ll show you a variety of ways to add indexes in SQL and then I’ll offer some ideas for when you want to use them.
Adding an Index When You Create a Table
Just as there are multiple types of indexes there are multiple ways to create or add them to MySQL tables. One way is to add an index when you first create a table.
Here’s a simple table containing three columns to hold the first and last name and email address of a customer. I’ve added an index to the Email column.
[code type=”mysql”]
CREATE TABLE Customer(
LastName CHAR(30) NOT NULL,
FirstName CHAR(30) NOT NULL,
Email CHAR(50) NOT NULL,
INDEX (Email)
);
[/code]
Pretty simple. You create an index with an INDEX statement and you add a parameter for the column to be indexed. You can also add a primary key instead of an index.
[code type=”mysql”]
CREATE TABLE Customer(
LastName CHAR(30) NOT NULL,
FirstName CHAR(30) NOT NULL,
Email CHAR(50) NOT NULL,
PRIMARY KEY (Email)
);
[/code]
Similarly, to add a Fulltext index:
[code type=”mysql”]
CREATE TABLE Post(
post_author CHAR(50),
post_date DATETIME,
post_content LONGTEXT,
FULLTEXT (post_content)
);
[/code]
To create a descending index (and assuming you’re working with MySQL 8 or above) you add DESC to the name of the column to index.
[code type=”mysql”]
CREATE TABLE Customer(
LastName CHAR(30) NOT NULL,
FirstName CHAR(30) NOT NULL,
Email CHAR(50) NOT NULL,
INDEX (Email DESC)
);
[/code]
Adding a multi-column index is very similar. You separate each column with a comma. And like single column indexes, you can also add a primary key as opposed to an index.
[code type=”mysql”]
CREATE TABLE Customer(
LastName CHAR(30) NOT NULL,
FirstName CHAR(30) NOT NULL,
Email CHAR(50) NOT NULL,
INDEX (LastName,FirstName)
);
[/code]
Adding an Index to an Existing Table
Adding an index to an existing table is just as simple and there are a variety of ways you can create the index. But first, you might want to know if the existing table already has any indexes.
[code type=”mysql”]
SHOW INDEX FROM table_name;
[/code]
Here’s an example using the wp_postmeta table in WordPress
[code type=”mysql”]
SHOW INDEX FROM wp_postmeta;
[/code]
And the results from running the query through phpMyAdmin.
You can see three indexes (the Key_name column), one of which is a primary key, along with their associated columns. The index on metakey allows for a NULL value, though the other two do not. Also note that all three indexes are built using a BTREE structure.
There are two different ways to add indexes to existing tables. The first uses the CREATE command, which can be used to create both regular and unique indexes. You invoke the command, a name for the index, and the table and column to add the index to.
Creating Indexes with the CREATE Command
[code type=”mysql”]
CREATE INDEX index_name ON table_name(column_name);
CREATE UNIQUE INDEX index_name ON table_name(column_name);
[/code]
Here are a couple of variations. The first adds the index to multiple columns. The second specifies the structure for the index at the end.
[code type=”mysql”]
CREATE INDEX index_name ON table_name(column1, column2, column3);
CREATE INDEX index_name ON table_name(column_name) USING BTREE;
[/code]
If the column contains string values you can set the index to only include the first (n) characters of the string
[code type=”mysql”]
CREATE INDEX index_name ON table_name(column_name(n));
[/code]
These variations also work with unique indexes as well as regular indexes.
And of course you can add a Fulltext index to an existing table as well.
[code type=”mysql”]
CREATE FULLTEXT INDEX index_name ON table_name(column_name);
[/code]
Adding Indexes with ALTER TABLE and the ADD Command
One thing you can’t do with the CREATE command is add a primary key. For that we’ll have to consider the other general method for adding an index to an existing table. It uses the ALTER TABLE command to ADD INDEX or ADD UNIQUE INDEX, ADD PRIMARY KEY, or ADD FULLTEXT.
[code type=”mysql”]
ALTER TABLE table_name ADD INDEX (LastName, FirstName);
ALTER TABLE table_name ADD UNIQUE INDEX (EmailAddress);
ALTER TABLE table_name ADD PRIMARY KEY (ID);
ALTER TABLE table_name ADD FULLTEXT (post_content);
[/code]
With the alter table method you don’t specify a name for the index. MySQL creates one automatically, though you’ll have to use the SHOW INDEX command after you add the index to see the name if you’re curious what it is.
If you want to remove an index, you can do that using two similar methods.
[code type=”mysql”]
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP INDEX PRIMARY KEY;
[/code]
Adding Indexes to WordPress Driven Sites.
Since the odds favor you running a WordPress site somewhere, you might want to read up a little on how WordPress organizes the database into different tables. A few of the tables don’t have any indexes associated with them, though an index here or there might help speed things up.
Adding an index to WordPress is the same as adding an index to any table in your database. Some people think more could be added than what WordPress comes with by default. Here are a few articles that talk about adding an index to specific WordPress tables that might benefit from one.
- Add MySQL Index to WordPress wp_options Table
- Add indexing to meta_value in wp_postmeta
- How to Clean up Your wp_options Table and Autoloaded Data
- Index Autoload Plugin
Tips for Working with Indexes
A couple of weeks ago I mentioned some of the reasons for using indexes and even though it means repeating myself a bit, I wanted to mention a few of them again to remind you when you probably want to use an index.
Remember that indexes work best to read data. They work best with SELECT statements. Because indexes are separate tables, when the data in the column or columns being indexed change, MySQL needs to reindex them. So think twice before using indexes on columns you frequently UPDATE, INSERT or DELETE.
SELECT statements with WHERE clauses are particularly good candidates for indexes. The index helps to quickly find the desired rows without having to do a full row-by-row table scan.
Indexes can also be useful when you want to sort the results of a SELECT query with ORDER BY or something similar. Using an index eliminates an extra sorting step speeding up the query.
It’s good practice to add an ID field or column to every table and to add an index, usually a primary key, to it. It’s good to use unique indexes or keys whenever a column should contain unique values or when the values should be NOT NULL.
Whenever you use column JOINs and both columns are of the same datatype, you likely want to add an index to the columns. You do have to make sure the columns are of the same type (int, char, etc.). For example, the two name columns in the following query make for good index candidates.
[code type=”mysql”]
SELECT name FROM users
LEFT JOIN orders ON (users.name = orders.name)
WHERE users.id = $userid;
[/code]
You want to avoid duplicating indexes. You don’t need or want multiple indexes for the same query as each takes up disk space and the extra indexes can slow things down a little.
Also remember that for small tables you probably don’t need an index regardless of the specifics. When there are only a few rows, it’s quicker to do the full table scan than to create and work with indexes.
I’ll leave you with a few links to articles with more. The first works through quite a few specific queries and talks about how to index them.
- Building the best INDEX for a given SELECT
- 3 ways MySQL uses indexes
- 3 Ways to Optimize MySQL Performance: Indexing
- Indexing Dos and Don’ts
Closing Thoughts
Hopefully, over the last few weeks, I’ve convinced you of the benefit of using indexes. In general, they speed up searches of your database by allowing MySQL to organize your data in the best way for different queries.
Speaking of queries, I have one more topic left about SQL databases and that’s queries. In a few weeks I’ll talk about how you can find your worst performing queries and then I’ll offer tips for writing better and faster executing queries.
Download a free sample from my book, Design Fundamentals.
Searched for how to add multiple indexes to a table and your page was by far the simplest and best. Thank you for sharing your knowledge.