MySQL indexes can save your web app.

[administrator]

I recently had trouble with a web app that was tanking at peak usage times. After considering all the major fixes, I decided to look at the basics: the database tables. 

I figured out which table was giving me problems, and immediately realized it had not been properly indexed. Indexing a database table is like creating an abbreviated version of the table, that allows for super fast reads. It can really speed up your app. 

So, I applied a couple of proper indexes (takes a few seconds to do,) and all of a sudden, an app that was running CPU at 50%, dropped to 3% and ran much faster. Don't underestimate proper indexing!

This is something basic that just slipped through the cracks. Check out the images below: pre indexing and post indexing. The graphs tell the story.

From the Google:

MySQL uses indexes for these operations:

• To find the rows matching a WHERE clause quickly.
• To eliminate rows from consideration. ...
• If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. ...
• To retrieve rows from other tables when performing joins.

I hope you find this useful.

Stef