In this article you will get the knowledge about very important function inside SQL databases.
We will talk about indexes.
WHAT IS A SQL INDEX?
SQL Index is a quick lookup for the records which we want to search frequently.
WHAT TYPES OF INDEX WE CAN RECOGNIZE?
The most common index in modern sql database is clastered index. Clastered index defines a physical order of the data inside a table. There is only one clastered index per table, usually it is our primary_key column (f.e. id).
We also can recognize non-clastered indexes. These are very important if we have very large database with millions of records. In this situation searching for a data is getting slower and slower. Non-clastered indexes can make searching a lot faster.
HOW NON-CLASTERED INDEX WORKS?
A non-clastered index does not define the order of the records. These indexes are even stored in a diffrent place. As same as books have indexes where we can search for a data very fast, sql database has its own indexes which we can build.
The command to add index is:
CREATE NONCLUSTERED INDEX <index_name>
ON cars(name ASC)Characteristics of those indexes:
- Creating a non-clastered index will take a time - sql engine will search through the table multiple times to get all the data needed to create an index
- we can create a non-clastered index including multiple columns inside a table, we should be careful tho, because it can take a lot of storage on our database disk
- INSERT/UPDATE records are faster with a non-clastered index - we don't needy an actual data, we only use the data from index to update the record
- with SELECT clause it depends - if we search only the column included inside index, it will be incredibely fast. But if we search also other columns, then it depends. Usually sorted data inside index will also help us make query faster, but anyway, it needs to make 2 queries - one into index, and second one into the actual database so it can be tricky.