SQL Index Tutorial | SQL Optimisation Part 1

SQL index is a distinct structure in the database that is built using the CREATE INDEX statement. Mostly , DBAs love & hate SQL indexes at the same time due to the tricky nature they posses. They can either speed up your databases or slow it down , depending on how correctly you have implemented them. People familiar with programming languages understand the behaviour of pointers very well. An index is the pointer in database.

WHAT IS SQL INDEX

As said , SQL indexes are a distinct data structure such as B+tree or balanced tree where reference to the original data is stored with the keys.  It requires its own disk space. Creating an index does not change the table data; it just creates a new data structure that refers to the table. You can also put it as SQL Index are special lookup tables that the database search engine can use to speed up data retrieval.

SQL INDEX CREATE Command

Syntax to create SQL Index is as follows :

 CREATE INDEX index_name ON table_name;

You define index on columns and you can do it two ways

*Single-Column Indexes

Declare index on one column only :

 CREATE INDEX index_name ON table_name (column1) ;

*Composite Indexes ( Multiple Columns)

Declare index on more than one column

 CREATE INDEX index_name ON table_name (column1) ;

Implicit Indexes

Implicit indexes are indexes that server creates automatically  upon creation of the object . Indexes are automatically created for primary key constraints and unique constraints.

SQL INDEX DROP Command

You can Drop index with the below code

 DROP INDEX index_name;

4 Scenarios WHEN SQL Index is Not An Option

  1. When tables are too small
  2. Tables have frequent, large batch updates or insert operations.
  3. When columns contain a high number of NULL values.
  4. Manipulation frequency of Column(s) is high.

SQL indexes are very important when it comes to the optimisation of database and enhancing query performance. The selection criteria for index and the columns which should be selected for index , needs a special consideration. This is the reason we have decided to go deep with this topic and write separate posts :

  1. How Does an SQL Index work.
  2. Clustered & Non Clustered Indexes.
  3. Fragmentation of Indexes

We will see how an SQL index sort data and how it helps to optimise the performance of the database. How data is organised in data structures and what will happen upon each read /write operation.

At the end we will discuss types if indexes with practical examples because then you will be able to understand why DBAs love & hate SQL .

Stay Tuned for next articles!

Since you are here. Support BI Talks BI in efforts to meet the Maintenance costs so that we can keep serving you up with the latest articles.

Advertisements

Leave a Reply

%d bloggers like this: