How to improve SQL query performance with Indexing

How to improve SQL query performance with Indexing

In this article, we will focus on how to optimize SQL queries with indexing to gain better performance. As you may already know, indexing is used to speed up searching databases. If you are not familiar with the concept of indexing, you can visit this web page and learn the basics of indexing. There are multiple tips that you need to know as a developer when it comes to fine-tuning query performance using indexes. Let’s discuss those techniques one by one.

Combining DDL (Data Definition Language) commands          

In SQL, DDL commands are used to modify, add or remove tables in a database. By default, executing these DDL commands introduces blocking. Besides, the “Alter” table command requires a temporary table as MYSQL doesn’t perform “Alter” table commands in memory. Due to these facts, altering large tables can consume more time and disk space. Depending on your MYSQL version, table altering such as adding and modifying indexes can be performed in line, while some still use the table copy method. 

Combining all your table altering commands together is a fine query optimization technique. For instance, let’s consider a scenario where you need to create an index, modify the index and add another column to the index. If you are doing these using separate commands, you can write them as follows.

 

  1. ALTER TABLE <table_name> ADD INDEX (<index_name_1>);
  2. ALTER TABLE <table_name> DROP INDEX <index_name_2>, ADD INDEX <index_name_2> (<index_column_1>,<index_column_2>);

However, you can get better performance by combining queries as follows.

ALTER TABLE <table_name>

ADD INDEX (<index_name_1>),

DROP INDEX <index_name_2>,

ADD INDEX <index_name_2> (<index_column_1>,<index_column_2>);

This technique helps to make a significant improvement in the performance of the query.

Removing duplicate Indexes 

Having two identical indexes makes a negative impact on the performance of SQL queries. It is actually a waste of disk space and also slows down the insertions to the table. Therefore, it is a good practice to avoid duplicate indexes to eliminate these issues. Duplication of indexes can happen in multiple ways.

 

  1.  Using a primary key as an index.

This is one of the most common mistakes you can make while indexing. To understand this further, have a look at the following create table query.

 

CREATE TABLE Persons (

PersonID INT NOT NULL UNIQUE,

LastName VARCHAR(255),

FirstName VARCHAR(255),

Address VARCHAR(255),

City VARCHAR(255),

           INDEX(PersonID)

);

 

Here you can see that the PersonID is the primary key of the table and we have set a separate index with that primary key. In MySQL, the primary key need not be indexed. So we can remove the line “INDEX(PersonID)” in this DDL.

 

2) Indexes with the common left most clause

This is another common cause of index duplication. You can understand it easily by referring to the following example.

 

CREATE TABLE Persons (

PersonID INT NOT NULL UNIQUE,

LastName VARCHAR(255),

FirstName VARCHAR(255),

Address VARCHAR(255),

City VARCHAR(255),

           INDEX index_one (FirstName, LastName),

           INDEX index_two (FirstName, Address),

           INDEX index_three(Address)

 

);

In this DDL, there are three indexes. As you can see, the leftmost clause of both index_one and index_two is “FirstName.” This can be considered as an index duplication. So you will have to remove one duplicate index from those two indexes in order to optimize this query. Since there is no duplicate for index_three, it doesn’t need to be removed.

 

3) Be thoughtful when creating indexes

It is essential to select the optimal indexes when optimizing queries using indexes. Furthermore, you need to pay attention to the WHERE clause, GROUP BY clause, ORDER BY clause, and the joins of the select queries when deciding indexes.  

Once you finalize indexes for the table, consider other indexes required in the application and try to combine indexes as much as possible. Additionally, make sure to remove all the unused indexes. 

Having more indexes can also backfire the performance as they can slow down operations such as INSERT and UPDATE. So ensure that you also take into account this drawback when creating indexes.

4) Make it work

When you use indexes in a query, you need to identify the correct way of using them and placing them in the query. If you did not use the index properly and correctly in queries, the database would not use it, and there will be no performance gain. In this subsection, we will discuss some common mistakes that you should avoid when using indexes in your queries.

 

 Do not use indexed columns with functions

 

Let’s understand this using an example. 

 

SELECT COUNT(*) FROM Area WHERE COS(length_in_meters) > 0.2;

 

This query is simply used to get the number of items which has the COS value of the length higher than 0.2. Here, COS is a mathematical function used in mathematics. Assume that the column length_in_meters is an indexed column. You might think that this query is optimized and will increase the performance since we have used an indexed column inside the WHERE clause. 

Yet, it will not bring any performance improvement as we have used the indexed column inside a function there. So, what should be done to optimize this query? You need to index the output or the result of the function. In this case, you can use the concept of Generated Columns.

 

Do not use OR clause

Look at the following query.

SELECT * FROM Persons WHERE first_name = ‘Alex’ OR last_name = ‘Gomez’;

 

You may think that it will optimize the query and improve the performance if we use indexes in one or both of these first_name or last_name columns. However, we can’t make full use of indexes in this case since there is an OR clause in the query. 

So how can we utilize indexes when we need to optimize a query with OR? The simplest way is to use an alternative method such as ‘UNION’ join instead of the OR clause. Let’s see how to rewrite the same query with UNION join. 

 

SELECT * FROM Person WHERE first_name = ‘Alex’

UNION

SELECT * FROM Person WHERE last_name = ‘Gomez’;

Cool, right?

There’s no need to always stick to this union method, and you can also use the UNION ALL method as per your requirement. 

 

Do not use LIKE clause wildcards in WHERE clause

 

Have a look at the following query where we have used a wildcard.

 

SELECT * FROM Person WHERE first_name LIKE ‘%Al%’;

This query basically returns all the details of the people whose first name starts with ‘Al.’ However, if there is a wild card at the beginning of the searching pattern, the database does not use indexing for that search even though first_name is an indexed column.  Thus, there won’t be any optimization in this query.

Therefore we should avoid using wildcards with the indexed columns as much as possible. Otherwise, you can use full text indexes to optimize such queries. 

There are many more methods that can be used to optimize your queries using indexes such as, 

  • Use sortings in the GROUP BY clause in the same order. This means that if you are sorting multiple indexed columns and sorting the first column in ASC order, you should not sort the next column in DESC order. It can reduce the performance of the optimized query. 

 

Conclusion

In this tutorial, we had a look at how to perform query optimization in MySQL using indexes. There are several important facts you need to be aware of when using indexes to improve the performance of queries. Always be careful what the exact indexes you need and define an index only if you need it. The reason is that indexes can slow down the inserting and modifying functions even though they improve the performance of searching data. Therefore, ensure that you have proper knowledge of optimizing queries and indexes before practically applying them in applications

Are you looking for Tech Jobs?
Discover more on Meritocracy.is!

Leave a Reply

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