A MySQL database table index example

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 ftp_files:

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)

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_command and 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.