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.

UK Electronic Designer
USEFUL?
We benefit hugely from resources on the web so we decided we should try and give back some of our knowledge and resources to the community by opening up many of our company’s internal notes and libraries through mini sites like this. We hope you find the site helpful.
Please feel free to comment if you can add help to this page or point out issues and solutions you have found, but please note that we do not provide support on this site. If you need help with a problem please use one of the many online forums.

Comments

Your email address will not be published.