Basically an index on a table works like an index in a book. They are a way to avoid scanning the full table to obtain the result that you’re looking for. Say you have a book and you want to find some information about something particular. Without an index you’d have to go through the pages one by one, until you found the topic – a full table scan. An index has a list of keywords, so you’d consult the index and see that storage is mentioned on pages 1, 83, 204, etc. You could then flip to those pages directly, without searching.
The downside of indexes is that they slow down adding data as entries are added to the indexes, so they should only be used when the will be really helpful for searching.
Once created MySQL will use an index automatically if it will help with a search.
Index types
B+Tree index
The most common index type. Stores the elements in a sorted order.
The problem about this index type is that you have to query for the leftmost value to use the index. So, if your index has two columns, say last_name and first_name, the order that you query these fields matters a lot and you must query last_name first.
REMEMBER – YOU NEED TO QUERY YOUR COLUMNS IN THE SAME ORDER AS DEFINED IN THE INDEX TO USE IT!!!
Hash index
Only the memory backend supports. It’s lightning fast but only useful for full lookups (you can’t use it for operations like >, < or LIKE).
Since it only works for the memory backend, you probably won’t use it very often. A typical case might be creating a temporary table in the memory with a set of results from another select and perform a lot of other selects in this temporary table using hash indexes.
Indexing DateTime
MySQL recommends using indexes for a variety of reasons including elimination of rows between conditions: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
A datetime column an excellent candidate for an index (e.g. NORMAL + BTREE) if you are going to be using it in conditions frequently in queries on tables with large numbers of rows. Without it, if your only condition in queries is a DateTime and you have no other index in the condition, MySQL will have to do a full table scan on every query.