importance of a INDEX in mysql

[hijinks]

A lot of people that are getting into PHP are also getting into mysql for the first time. Generally they can build a database and create a good app that works. I have seen a lot of popular scripts that don't use a index on their tables. A index in mysql is kind of like a table of contents in a book. It helps mysql find what its looking for faster.

 

Generally a INDEX should be placed on a column in a table that is going to be searched a lot. So for examle I have made a test table and filled it with random data. I have a column called id which should be unique to each user. So this is a key example of a column that would be used in a where section of a query often. Like this

 

SELECT * FROM test WHERE id=5432;

 

For now we won't limit it since I want to show how fast a index can make a query. So for this example I have a table called test that I have filled with close to 900k rows. Here is a query I'm doing to try to pull a row from a table with no index's.

 

Code:

mysql> select count(*) from test where id=3232301;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (5.97 sec)

 

So that query took almost 6 seconds to run. Just think you created this great forum software and someone that downloaded your forum has a killer forum that has 500k posts. So each time you need to load a page it could take 4 seconds of CPU time per request. Thats A LOT of time. Your server load will shoot through the roof and some people will begin to timeout on their connections.

 

So lets alter this table and set the column id to be a index.

 

Code:

mysql> ALTER TABLE test ADD INDEX(id);
Query OK, 853614 rows affected (48.27 sec)
Records: 853614  Duplicates: 0  Warnings: 0

 

Ok so now lets run our same query as before and see the speed increase;

 

Code:

mysql> select count(*) from test where id=3232301;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.07 sec)

 

WOW.. we are WAY below the close to 6 second query time. Now that is fast.. so people if you are created a webapp that uses mysql and expect to have a load of rows in a table USE INDEX's!

 

As a admin I'm sick of fixing people's mistakes cause its their bad database design that's killing server's i work on.