Indexing MySQL fields to speed up PHP queries

16 March, 2012 by Tom Elliott

If you are using fairly complex SQL queries in your PHP which involve bringing together fields from two or more tables in a database and you are not using indexing, you probably should be!

Indexing is the process of increasing the speed of information retrieval by assigning a specific field as an ‘index’ which creates an ordered data structure that is quickly referenced using pointers. You don’t need to know exactly how indexing works to use it effectively (neither do I for that matter).

If you’re in the habit like me of setting up each database table with a PRIMARY KEY field then you will already be using indexes since the primary key field – usually an integer ID – is indexed automatically. There’s a good chance that many of the queries used in your PHP application won’t need optimising as they probably already use this indexed field to select specific rows or order the data.

You could consider adding indexes to any table fields that are used to filter your data by. One of the most common places I’ve seen significant (over 10x) speed improvements in PHPMySQL execution is by adding an index to fields used within JOIN statements in an SQL query.

Let’s consider the following scenario – a postcode lookup function using two tables. The first table stores all company contact details including postcode and the second table has all postcodes with their corresponding longitude and latitude values. A LEFT JOIN is needed to bring all contact details together for each company that match the corresponding postcode values in the second table. As there are a few hundred contact details and thousands of postcodes, non indexation would mean slower lookups.

Here is the SQL query:

SELECT tbl_contacts.company, tbl_contacts.address, tbl_contacts.postcode, 
       tbl_postcodes.longitude, tbl_postcodes.latitude
FROM tbl_contacts
LEFT JOIN tbl_postcodes
ON tbl_contacts.postcode = tbl_postcodes.postcode
ORDER BY tbl_contacts.postcode DESC";

As tbl_contacts.postcode and tbl_postcodes.postcode are the two fields that create the JOIN, indexing them in both the contacts and postcode tables therefore can greatly increase lookup speeds. It doesn’t matter that the postcode is of VARCHAR data-type, as indexes can be applied to most data types including VARCHAR, INT and DATE

To add the index to these fields is straightforward. If you are using PHPMyAdmin, select the structure tab for the table and then the index button which is the one with the lightning symbol. Once indexed, the indexed field should appear at the bottom of the table structure along with any other primary keys or indexes.

Worth noting:

• The more indexes you add, the longer it takes for any update or insert commands to completed, as the index also needs to be updated
• Adding indexes also increases the storage space used by the database.

Usually the extra space and time to updatedelete is negligible and greatly outweighs the speed benefits. Happy indexing!

If you have a WordPress site, the process of indexing is even easier with a plugin. Check out my post on slow WordPress websites

---

---