SQL query works fast on a small data sets or table with less number of rows, but in the real world database table contains millions of rows and hundreds of tables and we if try to apply a search query on a table which contains millions of rows then the SQL engine would take a lot of time to fetch the data from the table. For instance, if you perform a COUNT query on a table with 200 million rows then it would probably take 4 to 5 seconds to show the result, 4 to 5 seconds might not seem a heck of a deal but here we are talking about a single query, what if we try to perform a JOIN operation or some other query which has more than one statements there the SQL engine would easily take 20 to 30 seconds.
To resolve this problem we use SQL INDEX. Indexes in SQL are used to speed up the SQL queries on database tables, the concept of SQL INDEX is similar to the index of a book. For instance, if you want to learn about the Algebra you would not flip through your entire math book page by page scanning for the term Algebra, instead, you will go to the index page and look for the list of pages and there you search for the Algebra, and this similar technique is used by the SQL database itself.
When we create an Index, the database will generate a method to find the data based on one or more than one column.
When Should we Create index?
- When there is a lot of records in the database table
- When columns do not contain too many NULL values
- When there are complex queries performing on the database.
Always try to avoid index if there are less number of records in the database because INDEX occupies space in ht memory and slow down the process of INSERT and CREATE query.
CREATE INDEX Syntax
INDEX is used on tables columns so we can create indexes once there is a Table.
CREATE INDEX index_name ON table_name (column_name_1, column_2_name);
For the example consider this table Students
+------+--------+------+--------+-------+----------+ | id | name | age | grades | marks | Trade | +------+--------+------+--------+-------+----------+ | 1 | Luffy | 16 | A | 970 | Science | | 2 | Naruto | 18 | A | 960 | Humanity | | 3 | Zoro | 20 | A | 940 | Commerce | | 4 | Sanji | 21 | B | 899 | Humanity | | 5 | Nami | 17 | B | 896 | Science | | 6 | Robin | NULL | B | 860 | Humanity | | 7 | Goku | 27 | B | 860 | Humanity | +------+--------+------+--------+-------+----------+
<Note> There is no use of creating Indexes for this small table, there would be no speed change in data retrieval.
Query: Create an Index “in_name”on the column “name” in the “Students” table
CREATE INDEX in_name ON Students(name);
Types of Index
There is mainly 3 types of INDEX:
- Single Index
- Unique Index
- Composite Index
1. Single Index
If we create an Index for Single only one table column, then it would be a Single INDEX.
CREATE INDEX index_name ON table_name (column_name);
2. Unique Index
Unique Index used in that column where we want no duplicate values.
CREATE UNIQUE INDEX index_name ON table_name (column_name);
3. Composite Index
In the Composite index, we create indexes for more than one column.
CREATE INDEX index_name ON table_name (column_name_1, column_name_2);
Deleting the INDEX
When we do not require index for a column then using the DROP command we can delete the column INDEX.
The Syntax for dropping the INDEX vary from RDBMS to RDBMS.
DROP INDEX index_name ON table_name;
DROP INDEX index_name;
DROP INDEX table_name.index_name;
ALTER TABLE table_name DROP INDEX index_name;
- Indexing is used on SQL database columns to speed up the data retrieval.
- Indexing occupies space in the memory.
- Only use indexing if there is too much data on the table.
- Indexing can reduce the speed of Create and insert statement.