Web developers who are fairly new to MySQL will eventually come across a day when their web server is doing some serious lagging.  Traffic is still relatively low so you are wondering why the heck the server is so slow at loading the webpage.  There is a very good chance that the remedy for this problem is creating index's for your tables.

I know this has been talked about over and over on other sites, but the problem is that they are written by uber geeks who use language that makes understsanding it needlessly difficult.

The book I read explain and index like this. . .think of a MySQL index as an index on a book.  If you have no index, then you have to manually look through every page and have no idea where the topic about "Bill Gates" appears.  If you do have an index then you know exactly that Bill Gates appears on a certain page range so the other pages are skipped over and make finding it A LOT faster.

Adding indexes is one of the simplest and quickest things you can do to boost performance.  The question is how? 

First thing is first. I suggest downloading and installing SQLyog to handle all of your MySQL needs.  On your webserver you will have to login to cpanel to give your home IP remote access to your database.  (If you are using cpanel).

I don't plan on going step by step with how to create an index but I will say that all you have to do is right click on the table and click "Manage Indexes".  Then it will show you a list of fields to pick and you have to decide the order and which ones you'd like to use.  Pretty easy so far?

Now you might be wondering what is a good field to index.  Lets say you have a table of 50,000 rows with people's names.  You would definitely want to index their first and last names.  Let's say that you do a lot of queries that show how many people live in the state of NJ.  Add that field to be indexed as well. 

The rules are pretty basic.  Any time you keep using a certain field to ORDER BY, WHERE, or GROUP BY then you may want to index those.  Also, index's work from Left to Right.  For example, if you add a new index with the following fields: First, Last, State.  Your query should be going in that order to utilize the index. 

WONT WORK:  SELECT * FROM table WHERE state = 'nj'
WILL WORK: SELECT * FROM table WHERE first = 'bob'
WILL WORK: SELECT * FROM table WHERE first = 'bob' AND last = 'smith'

If you want to check to see if a query is using an index then add EXPLAIN to the start of it.  It will list "possible_keys" and if it is in use then an index will appear in the "key" field. 

That is all there is to it!  Your cheap webserver can handle your traffic, but it can't handle your crappy MySQL queries. :)