Speed Up Your MySQL Websites and Applications with Indexes



If you’ve been following me on Twitter you’ll have seen me mention MySQL indexes a number of times over the past month or two, this is because I’ve been working on optimizing the SendBeatsTo database. I wasn’t involved in the database stuff at the start of SendBeatsTo.

Usually when developing database driven websites I will add indexes to begin with so have never known their true power until now. In one case by adding a couple of Indexes I reduced the query time from 14 seconds down to 0.0008 seconds! Can you imagine that, 14 seconds to load one page? I’m sure it brings back memories of dial-up for some people.

What are MySQL Indexes?

MySQL indexes are like book indexes in that they let you find information quickly without having to go through the book (or rows) one-by-one until you’ve found what you’re looking for. It just needs to look up the Index and see XYZ is in rows 1, 45, 967.

When should you use MySQL Indexes?

MySQL.com has a great list of where MySQL uses indexes so I won’t repeat that. To briefly summarize the page, indexes should be used on columns you use WHERE clauses, JOIN, ORDER BY and GROUP BY a lot, note that there will be a performance hit if you update these rows frequently. Indexes speed up the previously mentioned functions but slow down INSERT and UPDATE as it’ll need to insert and update the index with each change so don’t add an index to everything!

Another thing to consider before deciding if you should add an index to a column is the size of the table. If you’re working with a small table it wouldn’t be beneficial to use an index, like you wouldn’t expect a 10 page book to have a 5 page index.

Too add an index to an existing table column run the following query:
[php]CREATE INDEX name_index ON table (column);[/php]

You can create an index on multiple columns by altering the above query slightly
[php]CREATE INDEX name_index ON table (column1, column2, column3);[/php]

To add an index while creating your table you simply need to add:
[php]INDEX (column1, column2)[/php]
to the end of your CREATE TABLE query.

Example

For those interested, the query I spoke about in the introductory paragraphs of this article was something similar to:
[php]SELECT count(DISTINCT bs.id ) AS bsCount, count(DISTINCT b.id ) AS bCount
FROM requests AS r
LEFT JOIN beats_shared AS bs
ON r.requestID = bs.request
LEFT JOIN beats AS b
ON r.requestID = b.request
WHERE r.requestID = “‘.$requestID.'”[/php]

Here I added an index to r.requestID, bs.request and b.request. These indexes made a difference because there are over 4500 requests (r), 150000 beats shared (bs) and 40000 beats uploaded (b), without the index each row from each table would have to have been scanned through one-by-one to get what I wanted (requestID). As a side note the query isn’t done like that anymore but the indexes do certainly still help!

As a bonus comment: If you are experiencing slow queries add EXPLAIN before your query and it’ll give you a table (not a database table – just a normal table) showing you what’s happening, this can also be useful for choosing what columns to add indexes to.

[php]EXPLAIN SELECT count(DISTINCT bs.id ) AS bsCount, count(DISTINCT b.id ) AS bCount
FROM requests AS r
LEFT JOIN beats_shared AS bs
ON r.requestID = bs.request
LEFT JOIN beats AS b
ON r.requestID = b.request
WHERE r.requestID = “‘.$requestID.'”[/php]

Let me know if you’ve got any questions or comments on MySQL indexes below…

Share This Post

About Iain Meddicks

Iain Meddicks is a father and music industry entrepreneur from Glasgow, Scotland. He is the founder of SendBeatsTo and CrazyPellas Music Community and has a passion for web technologies.

Visit Iain Online:
Iain Meddicks on Twitter Iain Meddicks's Website

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>