Optimizing SQL Queries

Working as a back-end developer you often needs to work with SQL queries and it is necessary to optimize your queries.

Query Optimization is a process of defining most efficient techniques that can be used to improve query performance. It is necessary to find a way to decrease the response time of query and identify the poor query performance.

Reducing response time, reducing CPU execution time and improving throughput are the major purpose to optimise SQL query.

In this blog we are going to know about few tips to optimise SQL queries.

Below are few tips you should take care in your SQL queries:-

1. Use SELECT column_name fields instead of SELECT *

Fetch data optimally from SELECT statement instead of selecting all data from table. Fetch only the necessary columns from the table, it helps to avoid the cost of transferring unwanted data and processing it.

Inefficient way:

SELECT * FROM employee;

Optimised way:

SELECT first_name, last_name FROM employee;

2. Avoid using SELECT DISTINCT

While using SELECT DISTINCT you can remove duplicates from the result, however it requires a lot of processing power to do this. Instead of using DISTINCT we can fetch more columns to remove duplicates.

Inefficient way:

SELECT DISTINCT first_name, last_name FROM employee;

Optimised way:

SELECT first_name, last_name, class, roll_no FROM employee; 

3. Avoid running SQL queries in loop

SQL queries inside a loop seriously impede performance, especially when server is not on local machine. Wrongly used, these queries unnecessarily consume CPU power, RAM and bandwidth.

Inefficient way:

foreach ( $all_users as $user ) {
    $query = 'INSERT INTO users (first_name,last_name) VALUES ("' . $user['first_name'] . '", "' . $user['last_name'] . '")';
}

Optimised way:

$user_data = array();
foreach ( $all_user as $user ) {
    $user_data[] = '("' . $user['first_name'] . '",  "'. $user['last_name'] . '")';
}
$query = 'INSERT INTO users (first_name,last_name) VALUES' . implode(',', $user_data);

4. Prefer SQL JOINS over Correlated sub-queries

Joins and Sub-queries both are used to fetch data from different tables into a single result, but using sub-queries where joins should be used will decrease the performance. Sub queries are easy to understand and read to beginners but by the time as an experienced person Join are the preferrable choice. Even Joins are faster than Sub-queries.

Inefficient way:

SELECT first_name, last_name FROM  employee WHERE id IN (SELECT id FROM salary WHERE salary > 5000);

Optimised way:

SELECT e.first_name, e.last_name FROM employee e JOIN salary s ON e.id = s.id WHERE s.salary > 5000;

5. Use EXISTS() rather than COUNT()

 While checking only for the existence of any matching data values you should use IF EXISTS instead of SELECT COUNT(*), IF EXISTS stops the processing of the select query as soon as the first matching row is found, whereas SELECT COUNT(*) continues searching until all matches are found.

Inefficient way:

IF (SELECT COUNT(first_name) FROM employee WHERE emp_id = 875) > 0

Optimised way:

IF EXISTS(SELECT first_name FROM employee WHERE emp_id = 875)

6. Use Wildcards wisely

Wildcard characters are used to search the matching pattern in SQL, they can use as prefix or suffix. Using wildcard(%) in combination with an ending wildcard will search all records for a match anywhere within the selected field. For example if we want to fetch all names that start with “mic”.

Inefficient way:

SELECT first_name FROM employees WHERE first_name LIKE ‘%mic%’;

This Will search all employee whose names start with “mic” like Michael, Mica, Micayle but it will also fetch results like Jaemica, Hermic and similar.

Optimised way:

SELECT first_name FROM employees WHERE first_name LIKE 'mic%';

This will fetch the desired result, we can use underscore(_) for one character like if we don’t know the first character but after that “mic” should be there.

Conclusion

So, we had a look why and how SQL query optimisation is necessary. Here are few more points to keep in mind:

• Use indexing properly, try to create more indexes for fetching composite columns.
• Try to use WHERE rather than HAVING. Only use HAVING for aggregated values.
• Understanding of columns datatype while comparing them.
• Avoid Use of too many JOINS.

We looked how minor changes in SQL queries can improve performance of your code drastically. You can use these points in your upcoming projects to increase project using experience.

For more details you can follow MySQL document. click here

Thanks for reading!!!