How to create MySQL field indexes

April 12th, 2009 by Ben Carigtan | File in: How-To

Are you frustrated that your query results are not returning as quickly as you would like in MySQL? As a web and database professional, I follow a rule of thumb that says if a page with a query is taking longer than viewers can hold their breath, then that means they need to be optimized – something needs to be done!

An index on the right field of a table is a sure way to speed up queries, assuming that you use that field to query the table. I will use phpMyAdmin to generate an index in this tutorial.

First open phpMyAdmin, login to your database and then choose a table where you want to create an index.

Study the “Indexes” panel on the lower part of the page. You should see the existing fields used as primary key or ordinary index:

The action icons are used for editing and deleting the index:

If you delete the index, it will only delete the index information and not the field on the table. To create a new index, click the Go button on the “create an index…” panel”

Enter the index name, usually it is the field name followed by your own index postfix, in the example below I used “_state”. Choose the field where you want the index to be created then click save:

You should see the new index appear on the Indexes list:

That’s it! You may use this tip to optimize your own table’s indexes. Remember to use the indexed fields on your queries so that you will benefit from them. Make sure to comeback to our page because we have a lot of other database optimization tips for you!