Networking

Unix and Linux network configuration. Multiple network interfaces. Bridged NICs. High-availability network configurations.

Applications

Reviews of latest Unix and Linux software. Helpful tips for application support admins. Automating application support.

Data

Disk partitioning, filesystems, directories, and files. Volume management, logical volumes, HA filesystems. Backups and disaster recovery.

Monitoring

Distributed server monitoring. Server performance and capacity planning. Monitoring applications, network status and user activity.

Commands & Shells

Cool Unix shell commands and options. Command-line tools and application. Things every Unix sysadmin needs to know.

Home » MySQL

Creating table indexes in MySQL

Submitted by on November 21, 2006 – 6:32 pm 9 Comments

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:

show databases;

 

Select your database:

connect ;

 

Get a list of tables:

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

describe photoarc_pictures;

 

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.

Print Friendly, PDF & Email

9 Comments »

  • Daniel Craig says:

    Hi there, I was looking around for a while searching for unsecured wireless networks and I happened upon this site and your post regarding , I will definitely this to my unsecured wireless networks bookmarks!

  • maskills24 says:

    I want to use in-memory tables for improved performance in MySQL. But what is the cleanest way to ensure that the tables reload (from on-disk tables) when the MySQL server restarts? Is there any way to configure this natively within MySQL, without resorting to Perl and similar external script hacks? Thanks

  • lildevilgurl152004 says:

    Hi, Im practicing in mysql and Im making a database. But What it keeps saying not indexed, what do I choose Unique or primary ?

    and when it ask you to create the TABLE , do I name the first column the same name as the table name? Because it keeps saying error table 1 ?

    Thanks!
    thanks for helping, so should i make it unique?
    I dont have a lock at all, is that okay? Im just practicing..this is my first time trying it out and its NOT the same stuff I learned on the 3schools site, so Iam confused from the start :) Please help, what do I do about the lock? Thansk

  • Noe R says:

    I want to make it so that people can search my mysql database. I thought of using WHERE field=’%$search%’ or LIKE but then if someone searched it would only search for that exact phrase for example if they searced for 1 2 and one of the entrys had 1 in it it would not find it because it would need to have 1 2 exactly not 1 or 2. How can i make it search so that it finds stuff even if it is not exactly the way they tiped it.

  • Noe R says:

    How can I efficiently get current information from a mysql database in my c# program?

    The program runs on several systems at once and each system needs to get fresh information in case the other systems have made changes.

    The program, in addition to normal operations that update the database based upon user activity, needs to draw out updated information from up to 5 tables simultaneously and display them to the user in a listbox.

  • andresumoza says:

    I need to have my users create username, and passwords, and i want thier email addresses. Everyone says to create a mysql database in phpadmin. I click datatabase then create new database, and when I try to add tables for each item i need it requests an index, then I keep getting error messages? Please tell me step by step how to create a new database in localhost/phpmyadmin?

  • Lucas H says:

    I would like a database that just allows storing data through forms and displaying it again. I have looked, but don’t really know what to look under. PHPMyAdmin is actually very close to what I want, but I need it to be for an end-user, not a developer. I would prefer Open Source if possible.

  • Heath says:

    Suppose you created indexes on some fields of a table using a SQL command, then one would want to see the existing indexes. How would one do this by PHPMyAdmin. I would be so grateful, if one would tell me how

  • Mistry says:

    Okay, i have a mysql database with a table called cmspage. in that i have id, title, desc, keywords, content.

    But how do i get it so for every new id is a new page.. ie:

    id=1 index..php
    id=2 about.php

    etc etc but does it automatically?

2 Pingbacks »

Leave a Reply to maskills24 Cancel reply

%d bloggers like this: