Jump to content

MySQL indexes can save your web app.


administrator

Recommended Posts

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

 

Screen Shot 2017-11-10 at 11.53.36 AM.png

Screen Shot 2017-11-10 at 11.53.57 AM.png

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...