Monday, September 29, 2008

MySQL Database Indexing

What is Database Indexing?
Database index is like data structure that improve the performance of a database.
Index on table can be created one or more column on databse table, which improves random access of any records and efficient access of ordered records.

Types of Indexes in MySql

1. Normal Indexes :-
Normal Index have not restriction like Uniqueness,it is a basic index.

2. Unique Indexes :–
It is like a Normal index but only one difference, all values of the indexed columns must only occur once.

3.Primary Keys :-
Primary keys are basically unique index and must be add "PRIMARY KEY" in specific column.

How to Create Index in Mysql at time of table creation.

CREATE TABLE student (
fname VARCHAR(30),
lname VARCHAR(30),
studID INT, INDEX (studID)

You can create index on existing table used following MySQL Statement

CREATE INDEX index_studID ON student(student)

Advantages of database index
1. Database indexes speed up the database selection operation.

Advantages of database index
1. Database indexes slow down the database insert,update,delete operations.
2. Database Index takes more space.

No comments: