Enhancing SQL query Performance

Posted By : Akash Poria | 27-May-2020

Enhancing SQL Query Performance

SQL is one of the most powerful and easiest data-handling tools. Everyone wants a quick response to their data retrieval process. So we need to design a database that provides the best performance during data manipulation which results in the best performance of an application. For best performance, we need to use the queries that are not only best and faster but also efficient. But there is no straightforward way to define the best performance but we can choose multiple ways to improve our SQL query performance.

 

Here is the list of tips which we can use while writing our SQL queries to improve the performance of our application.

 

Select specific columns instead of * while using SELECT statement.

Inefficient

         SELECT * FROM users; 

This query will pull in all the data from the users table whether we need it or not.

Efficient

        SELECT first_name, last_name, address, mobile FROM users; 

This query only pulls the required information.

 

Avoid using SELECT DISTINCT

SELECT DISTINCT is a useful way to remove duplicates from a query. SELECT DISTINCT works by grouping all the fields in the query to create distinct results. To accomplish this goal, a large amount of processing power is required. Additionally, data may be grouped to the point of being incorrect. One way to avoid using SELECT DISTINCT is to select more fields to create unique results.

Inefficient

            SELECT DISTINCT first_name, last_name, city FROM users; 

This query doesn’t account for multiple users in the same city having the same first name and last name. Popular names such as Raj Kumar will be grouped together, causing an inaccurate number of records.

Efficient

               SELECT DISTINCT first_name, last_name, city, state, pin, mobile FROM users;

By adding more fields, unduplicated records were returned without the use of  SELECT DISTINCT.

 

Try and Index all columns used in 'WHERE', 'ORDER BY', and 'GROUP BY' clauses.

Indexing allows MySQL server to fetch results faster from a database. MySQL indexes may take up more space and decrease performance on inserts, deletes, and updates but it will reduce select query execution time considerably.

 

Try and avoid LIKE expression with leading wildcards

MySQL will not be able to utilize indexes when there is a leading wildcard in a query. MySQL is going to scan all the row which will make the query slow.

 

Using Optimal Data Types

MySQL supports different data types including integer, float, double, date, date_time, varchar, and text, tinyInt etc. When designing your tables, you should always remember that "shorter is always better". For example, if we know that a client table will consist of less than 100 rows, we should use TINYINT for client_id instead of INT.

 

Try to avoid too many columns

Tables with too may columns can be extremely expensive and will require more CPU time to process. If possible, don't go above a hundred columns unless your business logic specifically calls for this. Instead of creating one wide table, try and split it into logical structures.

 

There are many more tips to enhance the performance of your SQL queries. I will share them in any other blog.

Happy Learning!! 

 

About Author

Author Image
Akash Poria

Akash is an experienced Java developer with experience in Web development using Spring,Hibernate. His hobbies are playing cricket and travelling to mountains.

Request for Proposal

Name is required

Comment is required

Sending message..