MySQL FAQ: How do I create a MySQL or MariaDB database table index?
Here’s an example of how to create an index on a MySQL database table. In this particular example my table uses the MySQL InnoDB database engine, but that doesn't matter too much — it just happens to be a MySQL database engine that supports foreign key relationships.
To get started, here's the SQL definition of a MySQL table I've named
CREATE TABLE ftp_files ( id int unsigned auto_increment not null, ftp_username varchar(32), ftp_command varchar(4), # STOR, DELE, RNFR, RNTO ftp_root_dir varchar(1024), # ex: /ftp/customer1/file1.pdf file_name varchar(1024) not null, file_size_bytes bigint unsigned not null, primary key (id) ) ENGINE=InnoDB;
As you can see, this database table describes the attributes of an FTP event, with all of the fields related to the concept of a file. (I've greatly simplified this from the actual table I use in production.)
A lot of people prefer to create their index in the
CREATE TABLE statement, but I prefer to add my indexes after the
CREATE TABLE statement, and I'll show my
CREATE INDEX definition next.
How to create a multi-column MySQL table index
Getting to the index ... immediately after my table definition I add the following line of MySQL code to create an index on my
ftp_files database table:
CREATE INDEX idx_ftp_files_command_and_dir ON ftp_files(ftp_command, ftp_root_dir);
This creates an index over the two fields
ftp_root_dir. This index will improve the performance of any queries run against these fields. In my case I have a bunch of "worker" programs running against this table that keep running a query that looks like this:
SELECT * FROM ftp_files WHERE ftp_command='STOR' AND ftp_root_dir='/ftp/customer1'
(The actual situation is a little more complicated than this, but I'm trying to keep this simple.)
Because I know this table is going to get very large, and because I know this query is going to be run thousands of times each day, and response time is important, I create this index to speed up these queries.
As a final note, I created this index with the name
idx_ftp_files_command_and_dir, but you can create your own indexes with whatever naming pattern you prefer. I do suggest, however, making the names meaningful. You'll appreciate that later when you have a large number of indexes in your database.
How to create a single-column index
I've already shown how to create a multiple-column index on a MySQL database table. For completeness, I thought I should also show how to create a single-column index, which is actually slightly easier:
CREATE INDEX idx_ftp_files_username ON ftp_files(ftp_username);
This index would be helpful if I was running queries against this field, like this:
SELECT * FROM ftp_files WHERE UPPER(ftp_username)='FRED'
For more information
For more information on creating a MySQL index, I recommend the MySQL CREATE INDEX documentation page.