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!!
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
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.