-

Indexing in SQL

Let us understand how normal (without indexing) query runs. It basically traverse each rows one by one and when it finds the data it returns.
Without Index:
Without indexingSo suppose query is to find 50 , it will have to read 49 records as a linear search.
With Index:
enter image description here
When we apply indexing, the query will quickly find out the data without reading each one of them just by eliminating half of the data in each traversal like a binary search. 

Comments

Post a Comment

Popular posts from this blog

-
Image
DML OPERATIONS: Data Manipulation Language is a one of the Subset of SQL. These SQL commands are used to store, modify, and delete data from database tables. In this category we have INSERT, UPDATE, and DELETE commands. Different DML statements are • Select • Insert • Update • Delete Select:  The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set. The SELECT command is the most commonly used command in SQL. It allows database users to retrieve the specific information they desire from an operational database. Syntax: SELECT column_name(s) FROM table_name and SELECT * FROM table_name Example: consider the table student_details. To select the first name of all the students the query would be SELECT first_name FROM student_details; If we want to select all the columns from the table, we use the following SELECT statement: SELECT * FROM student_details; Insert:  The ...
Read more
-
Image
What is DMBS? A database management system (DBMS) is system software for creating and managing databases in the form of tables . The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data .
Read more
-
Image
CLUSTERED INDEX VS NON CLUSTERED INDEX Clustered Index Only one clustered index can be there in a table because of the sorted order Sort the records and store them physically according to the order Data retrieval is faster than non-clustered indexes Do not need extra space to store logical structure A primary key by default creates a clustered index on it. Non Clustered Index There can be any number of non-clustered indexes in a table because of the number of unique keys we can create. Do not affect the physical order. Create a logical order for data rows and use pointers to physical data files Data insertion/update is faster than clustered index Use extra space to store logical structure A unique key/keys by dafault created an unclustered index on it. In Depth Explanation: Clustered Technically speaking, a clustered index is a B-Tree data structure where all the rows in a table are stored at the leaf level of the index. In other words, a clustered ind...
Read more