A MySQL database table index example

How do I create a MySQL 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)
) 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_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'