Skip to content

DW BI MASTER

Just another WordPress site

Menu
  • Home
  • Sample Page
Menu

SQL Index

Posted on October 29, 2017
While executing SQL query, most important aspect to consider is performance.  So  how you achieve a good performance from database queries. Let us take the example of a book. To find any topic in a book we don’t need to reference every page of book. We refer to index and visit the topic which we need.
Similarly, one of the important parts of SQL query development and optimization is the creation of indexes. In order to create proper indexing strategies it is necessary to understand what is SQL index and how indexes work.

What is Index ?

INDEX is a performance optimization technique that speeds up the data retrieval process. It is a persistent data structure that associated with a Table (or View) in order to increase performance during retrieving the data from that table (or View). Indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row.

Strategies To Build Index :

  • Build index on columns which have datatype as integer
  • Order of the columns in the index is important. Always use the column with the lowest cardinality first, and the column with the highest cardinality last.
  • Column on which index is build should always be declared NOT NULL
  • Narrower indexes take less space,  which in turn means the query will run faster.
  • Indexes should not be build on small tables.

Syntax For Index :

Syntax to create Index is :

CREATE INDEX “index_name” ON “table_name” (column_name);

Syntax to create Index on two column is :

CREATE INDEX “index_name” ON “table_name” (column_name1, column_name2);

Syntax to drop Index is :

DROP INDEX index_name;

Next time if you are facing SQL performance issues, try creating index for your table.But remember build index only when required.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Recent Posts

  • What is Machine Learning ?
  • What is Data Lake ?
  • What is Change Data Capture ?
  • Introduction to Data Mining
  • How to Create Free Tier AWS Account

Archives

  • July 2021
  • May 2018
  • January 2018
  • November 2017
  • October 2017
©2022 DW BI MASTER | Design: Newspaperly WordPress Theme