What is SQL Indexes? Type of SQL Indexes.
Indexes are lookup tables that the database search engine
can use to speed up data retrieval. Put, an index is a pointer to data in a
table. An index in a database is very similar to an index in a book
An index helps speed up SELECT command and WHERE clauses,
but it slows down data input, with UPDATE and INSERT statements.
Indexes can be created or dropped with no effect on the data
Creating an index need 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 or descending order
The CREATE INDEX
Command:
CREATE INDEX
index_name ON table_name;
Single-Column
Indexes:
CREATE INDEX index_name on
table_name (column_name);
Composite Indexes:
A composite index is an index on two or more columns of a
table
CREATE INDEX index_name on
table_name (col1, col2);
Whether to create a single-column index or a composite
index, take into consideration the column(s) that you may use very often in a
query's WHERE clause.
Implicit Indexes:
Implicit indexes are indexes that are automatically created
by the database server when an object is created. Indexes are automatically
created for primary key constraints and unique constraints
The DROP INDEX
Command:
An index can be dropped using SQL DROP command. Care should
be taken when dropping an index because performance may be slowed or improved
DROP INDEX index_name
We should avoid indexes in.
Though indexes are intended to enhance a database's
performance, there are times when they should be avoided. The following
guidelines indicate when the use of an index should be reconsidered
- It should not be used on small tables.
- Tables that have frequent, large batch update or insert
operations.
- It should not be used on columns that contain a high number
of NULL values.
- Columns that are frequently manipulated should not be
indexed.
