What Is A SQL Index Used For?
We can create, alter, and delete database indexes using SQL, but what do we need really need to index for? The fact is, we need them for almost any relational database that currently exists. Whether it’s SQL Server, Azure SQL DB, Oracle DB, MySQL, SQLite, and others – it doesn’t matter. They all all need indexes for database and query optimization.
As a result, indexing ensures that when we write SQL queries and procedures they will perform efficiently. Most databases have indexes on them whether we know it or not. When we create primary keys on a table many databases will automatically index these columns. It’s a pretty standard rule that primary and foreign keys (aka id’s) are indexed on database tables.
Who Should Create Indexes?
As we mentioned, most relational databases need indexes. Usually a database administrator or a database architect are the ones to create them. Not to mention both careers make a very nice salary.
Related: Occupational Outlook On Database Administrators & Data Architects
However, we should be careful when creating indexes. We don’t want to index all columns as this could cause the opposite effect of what we want to achieve, which are performance gains.
Often SQL developers also create indexes. This is also fine based on the skill level of the developer. To reiterate, we don’t want to create indexes where they are not needed or create too many. It’s important to test queries after creating indexes so that performance differences can be seen. As a result, if we see the opposite effect and our query runtimes are longer we may want to revert these changes.
Will We Need Indexes In The Future?
Although many databases require someone to create indexes, this is beginning to change. Now, many databases contain features to automatically index fields. For example, Azure SQL DB has this feature. Another example, Snowflake DB by default does not require indexing. This is all done behind the scenes requiring less overhead and skill so that the focus can be on the actual data.
This doesn’t mean that a SQL index doesn’t exist in the database. It just means that databases are now smart enough to create and optimize on their own based on usage. In the past, this just wasn’t possible. With modern technology this is becoming increasingly common and will probably phase out this skill at some point in the next 5-10 years. This is just a prediction, but when I started my career I thought this would have started to happen even sooner. Snowflake DB has quickly scaled as a top tier cloud database and low maintenance and low costs are the biggest reasons.
How Do Indexes Help Make SQL Queries Faster?
Indexes allow us to optimize our SQL queries when executing them against a database. When we have fields such as dates and they are commonly used in SQL queries, indexing them can allow our queries to perform a number of times faster than without indexes. A database has an optimizer that will update database statistics behind the scenes when an index is applied.
Since power users and developers are the ones creating SQL queries, indexes are all based on usage. We just don’t apply them randomly. We tend to apply them to the most commonly used fields with a medium to high cardinality. It’s not that we can’t apply them to low cardinality fields, but it’s not as common.
If we have a database query that commonly uses filters or joins such as ‘CompanyName’ or ‘EmployeeNumber’ then we would want these fields indexed. We can then test the same queries that utilize these fields to ensure performance gains.
Final Thoughts
Indexes are fundamental in database development and design. They ensure that a relational database can perform efficiently. When overused or used improperly indexing can actually cause SQL queries to run even longer. As a result, creating indexes should be done with caution and proper knowledge. A database administrator usually grants to denies privileges to the appropriate users that should be allowed to create them. Remember, a SQL index is an important performance tool when put in the right hands. Let’s just not get index crazy!