SQL CREATE INDEX statement tutorial with MySql
Indexes are special lookup tables that the database search engine can use to speed up data retrieval.
They also organize the way in which the database stores data.
Indexes on a table is very similar to a catalogue in a library; if you don´t have a catalogue and I ask you to locate a specific book in the British Library in London, holding over 150 million items, you will have to look at every book of the library and that will take a time ;)
On the other hand, if you have a catalogue, you look up the book in the catalogue and you directly go to the indicated shelf.
Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index in place, the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster.
An index stores a pointer to the table row
How does it find the other values that are in the same row? Well, it’s quite simple – database indexes also store pointers to the corresponding rows in the table. A pointer is just a reference to a place in memory where the row data is stored on disk. So, in addition to the column value that is stored in the index, a pointer to the row in the table where that value lives is also stored in the index.
Imagine that our table "books" below, has thousands of registers and we want to find the author of the book "Android in 3 days". We can create an index with the column "title". This index is going to look like this;
|
|
Index
|
||||||||||||||
One of the values in the index for a "title" could be something like (“Android in 3 days”, 0×82829), where 0×82829 is the address on disk (the pointer) where the row data for “Android in 3 days” is stored. Without that pointer all you would have is a single value, which would be meaningless because you would not be able to retrieve the other values in the same row – like the "author" of a book.
SQL CREATE INDEX SYNTAX
Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending (ASC) or descending (DESC) order.
This varies amongst different databases, in MySQL 5.0 this words are parsed but ignored; the index values are always stored in an ascending way.
If a certain column in a SELECT statement is sorted in descending order, processing is quicker if a descending-order index is defined on that column.
The basic format of the statement is as follows:
CREATE INDEX <index_name>
ON <table_name> (<column_names>)
SQL CREATE INDEX EXAMPLE
SQL CREATE UNIQUE INDEX
Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there's an index.
SQL CREATE UNIQUE INDEX SYNTAX
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
You can create indexes at any time; you do not need to create all the indexes for a table right after the CREATE TABLE statement. You can also create indexes on tables that already have data in them. Obviously, creating a unique index on a table in which the column concerned already contains duplicate values is not possible. MySQL identifies this and does not create the index. The user needs to remove the duplicate values first.
The advantages of using an index do not come without a cost. Indexes take up disk space, and they will cause INSERT, UPDATE, and DELETE operations to go slower, since each time one of these operations is carried out, not only does the database system need to update the values in the table, but it also needs to update the indexes.
Note: The syntax for creating indexes varies amongst different databases. Therefore: Check the syntax for creating indexes in your database.
DROPPING INDEXES
The DROP INDEX statement is used to remove indexes.
DROP INDEX <index_name> ON <table_name>
| << Previous | Index >> |