What is Mysql Index

What are Indexes?

Indexes are organized versions of specific columns in your tables. MySQL uses indexes to facilitate quick retrieval of records. With indexes, MySQL can jump directly to the records you want. Without any indexes, MySQL has to read the entire data file to find the correct record(s). Here's an example.

Suppose we created a table called "people":

CREATE TABLE people (
 peopleid SMALLINT NOT NULL,
 name CHAR(50) NOT NULL
);

Then we insert 1000 different names into the table in a completely random, non-alphabetic order. A small portion of the data file may be represented like this:

402table1 (click to view image)

As you can see, there's no recognizable order to the "name" column whatsoever. If we create an index on the "name" column, MySQL will automatically order this index alphabetically:

402table2 (click to view image)

For each entry in the index, MySQL also internally maintains a "pointer" to the correct row in the actual data file. So if I want to get the value of peopleid when the name is Mike (SELECT peopleid FROM people WHERE name='Mike';), MySQL can look in the name index for Mike, jump directly to the correct row in the data file, and return the correct value of peopleid (999). MySQL only has to look at one row to get the result. Without an index on "name", MySQL would've scanned all 1000 rows in the data file! In general, the less rows MySQL has to evaluate, the quicker it can do its job.
Types of Indexes

There are several types of indexes to choose from in MySQL:

[Note: Full query lists and examples can be found at the end of this article.]

   1. "Normal" Indexes – "Normal" indexes are the most basic indexes, and have no restraints such as uniqueness. These can be added by creating an index (CREATE INDEX name_of_index ON tablename (columns_to_index);), altering the table (ALTER TABLE tablename ADD INDEX [name_of_index] (columns_to_index);), or when creating the table (CREATE TABLE tablename ( [...], INDEX [name_of_index] (columns_to_index) );).

   2. Unique Indexes – Unique indexes are the same as "Normal" indexes with one difference: all values of the indexed column(s) must only occur once. These can be added by creating an index (CREATE UNIQUE INDEX name_of_index ON tablename (columns_to_index);), altering the table (ALTER TABLE tablename ADD UNIQUE [name_of_index] (columns_to_index);) or when creating the table (CREATE TABLE tablename ( [...], UNIQUE [name_of_index] (columns_to_index) );).

   3. Primary keys – Primary keys are unique indexes that must be named "PRIMARY". If you have used AUTO_INCREMENT columns, you're probably familiar with these. These indexes are almost always added when creating the table (CREATE TABLE tablename ( [...], PRIMARY KEY (columns_to_index) );), but may also be added by altering the table (ALTER TABLE tablename ADD PRIMARY KEY (columns_to_index);). Note that you may only have one primary key per table.

   4. Full-text indexes – Full-text indexes are used by MySQL in full-text searches. Because full-text search is so new and would add unnecessary complexity to this article, I won't explain it here. Should you want more information, visit the MySQL documentation.


more information refer this link http://www.sitepoint.com/article/optimizing-mysql-application