SQL Index

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.