Tuesday, June 7, 2011

Purpose of MySQL INDEX with Example

MySQL Indexes

Using INDEX we can search and fetch the data from the table like getting a chapter or specific information from a book INDEX.

An index can be created in a table to find data more quickly and efficiently.

The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

SQL CREATE INDEX Syntax


Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name ON table_name (column_name)


SQL CREATE UNIQUE INDEX Syntax


Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name ON table_name (column_name)

Note: The syntax for creating indexes varies amongst different databases. Therefore: Check the syntax for creating indexes in your database.

CREATE INDEX Example

The SQL statement below creates an index named "LastnameIndex" on the "LastName" column in the "Persons" table:

CREATE INDEX LastnameIndex ON Persons (LastName)


If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

CREATE INDEX LastandFirstnameIndex ON Persons (LastName, FirstName)

How MySQL Uses Indexes

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.

Purpose of MySQL Index

Without INDEX:
If the number of records in a table is very less, then there is no need of using INDEX for a field in a table. Without INDEX also it will work very fast with less records table.

With INDEX:
If we want to know the real purpose of the MySQL INDEX when the records is more than 1000 records (say for example). Our system will get slow to fetch the records.

We can solve this problem by using several methods like:
a. Increase the memory_limit in our server
b. Increase the maximum_execution_time in our server
c. Optimize the Query like fetching the appropriate fields instead of Select * from ... etc..

But, We cant increase our System performance. So, now the time to introduce INDEX.

The performance of the table is really improve when using INDEX.

No comments: