Creating table indexes in MySQL
Creating an index for specific columns in a database table can greatly speed up search operations. This is important for any database query that use WHERE in the SQL string. Below is a practical example used to speed up a Coppermine Photo Gallery MySQL database.
Connect to MySQL:
mysql -u -p
Get a list of databases:
Select your database:
Get a list of tables:
The table for this example is called photoarc_pictures. This is a standard Coppermine Photo Gallery table that contains names of files, filepaths, and photo captions. Let’s get some details about this table:
I have a script that adds a caption for every photo in the database. The SQL portion of the script looks something like this:
UPDATE photoarc_pictures SET caption = "$CAPTION" WHERE filename = "$FILENAME" AND filepath = "$FILEPATH";
As you can see, this SQL query tries to search for a specific filepath and filename. If you have a large table, this operation may take some time and a lot of CPU power. What we need to do is to build an index for filepath and filename columns. Here’s how to do this:
CREATE INDEX filepath_index ON photoarc_pictures (filepath); CREATE INDEX filename_index ON photoarc_pictures (filename);
In this case the filepath and filename columns are varchar type. If they were BLOB values, the syntax for index creation should specify how many charactes to use:
CREATE INDEX filepath_index ON photoarc_pictures (filepath(255)); CREATE INDEX filename_index ON photoarc_pictures (filename(255));
This may take some time to complete for large table. Once it’s done, you will notice a significant improvement in performance for all SQL operations that try to match a specific filepath and filename value in this table.
If you modify the layout of your table, such as rearrange the column positions, you will need to re-build your indexes. To re-create an index, you first need to delete the existing index. In our example this would be done like so:
DROP INDEX filepath_index ON photoarc_pictures; DROP INDEX filename_index ON photoarc_pictures;
Indexes are a common part of any database, but they are often overlooked. The improvement in performance, brought about by a properly indexed data, can be substantial.