Indexing in MySql

Hello Friends,

Here i am with another new concept in MySql Index.


Do you understand what an index is and how it works?

An index is an ordering of (part of) the columns in a row to improve access speed to a specific row or subset of rows. As others have said, the purpose is performance.

There are two ways to implement an index:
(1) logically arrange the rows themselves according to the sequence defined by the index. This is a "clustered" index. Since the rows can only be arranged in one sequence, there can only be one "clustered" index.
(2) leave the rows alone, but create a separate structure made up of only the indexed columns and a reference to the actual row. Order these according to the sequence of the index. This is a "non-clustered" index. (Also known as "alternate index" or "secondary index".) This index has no bearing on the way the data rows are organized.

When you access data through a "non-clustered" index, the system does two record lookups: first, it searches the index to find the index columns that meet the predicate criteria. When it finds one, it uses the reference to go to the table itself and retrieve the actual row.

When you access data through a "clustered" index, the system does one record lookup: it searches the index to find the index columns that meet the predicate criteria. When it finds one, the rest of the columns (the actual row) are stored with the index.

So a clustered index is faster than a non-clustered index, but you can only have one. A non-clustered index is faster than reading the whole table if you are looking for a very small percentage of the rows. If you are looking for a large portion of the rows, it may be faster to skip the index and just read the whole table.

All of this is in the physical design of the table.

The primary key, on the other hand, is in the logical design of the table. The primary key is unique -- there can only be one row with a given primary key value. It identifies exactly one row.

In most databases, it makes sense to implement the primary key as a clustered index. Not all databases do this -- some implement the primary key as a non-clustered index, separate from the data rows.

Finally -- in some databases, a uniqueness constraint is implemented using a non-clustered index. This special-purpose index is not for performance reasons; rather, it is a physical mechanism for enforcing uniqueness.

In addition to the above post Here i am adding a URL which would guide you to the best knowledge in INDEXING Concepts in MySql. I went through the below link and it was really useful.

http://sqlserverpedia.com/wiki/Indexing_Strategies