Indexes
are used to find rows with specific column values quickly. It can execute the
performance of query within fewer seconds. You can find the difference of time
taken for a query to execute with and without index.
With index index it performs quickly and without index it takes some time. This is because without index the query has to scan all the rows of table ie. from first row to last row. With index it will start the search from the row where it find its column.
With index index it performs quickly and without index it takes some time. This is because without index the query has to scan all the rows of table ie. from first row to last row. With index it will start the search from the row where it find its column.
MySQL
uses various index like,
Primary Key
Unique Key
Secondary Index
Full Text Index
Why
MySQL Uses Index?
·
To
find the rows matching the WHERE clause quickly.
·
To
retrieve rows from other tables when performing joins.
·
To
sort or group a table if the sorting or grouping is done.
Primary
Key
It
must contain a unique value for each row of data. It cannot contain null values
There
can be only one primary key for a table. We can have primary key for one or
more columns.
Primary
Key can be referred as a foreign key.
*
a foreign key is a field
(or collection of fields) in one table that uniquely identifies a row of
another table
A example for primary key,
This example shows a table having with a primary key on two columns.
Unique
Key
It
is same as primary key but it can have null values. A table can have many
unique keys. And also unique cannot be referred as a foreign key.
The below example shows a table having with a primary key and unique key.
Secondary
Index
All
indexes other than primary key are secondary indexes. Ie. We can have only one
primary key in a table, if we want to create more than one index for a table we create it as
secondary index mentioning index.
Syntax;
Create index index_name on table_name
(column_name);
If
we don’t mention index_name mysql will automatically assign a index name based
on the column.(eg : if column name is ‘student’ it will create the index as ‘student’)
We
can also create secondary indexes for one or more columns.
The following example shows a table having secondary index for more than one column.
The following example shows a table having secondary index for more than one column.
Full
text Index
MySQL
supports Full-text indexing and searching.
A
full-text index in MySQL is an index of type
FULLTEXT
. It can be used only
with MyISAM
tables (until 5.5). From MySQL 5.6 and up, they can also be used with InnoDB
tables.
No comments:
Post a Comment