Working as a back-end developer you often needs to work with SQL queries and it is necessary to optimize your queries.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Reducing response time<\/strong>,<\/em> reducing CPU execution time<\/em><\/strong> and improving throughput<\/strong><\/em> are the major purpose to optimise SQL query. <\/p>\n\n\n\n <\/p>\n\n\n\n In this blog we are going to know about few tips to optimise SQL queries.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n Inefficient way:<\/strong><\/strong><\/p>\n\n\n\n Optimised way:<\/strong><\/strong><\/p>\n\n\n\n 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.<\/p>\n\n\n\n Inefficient way:<\/strong><\/strong><\/p>\n\n\n\n Optimised way:<\/strong><\/strong><\/p>\n\n\n\n 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.<\/p>\n\n\n\n Inefficient way:<\/strong><\/p>\n\n\n\n Optimised way:<\/strong><\/p>\n\n\n\n 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.<\/p>\n\n\n\n Inefficient way:<\/strong><\/p>\n\n\n\n Optimised way:<\/strong><\/p>\n\n\n\n While checking only for the existence of any matching data values you should use IF EXISTS instead of SELECT COUNT(*), Inefficient way:<\/strong><\/p>\n\n\n\n Optimised way:<\/strong><\/p>\n\n\n\n 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”.<\/p>\n\n\n\n Inefficient way:<\/strong><\/p>\n\n\n\n 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.<\/p>\n\n\n\n Optimised way:<\/strong><\/p>\n\n\n\n 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.<\/p>\n\n\n\n <\/p>\n\n\n\n So, we had a look why and how SQL query optimisation is necessary. Here are few more points to keep in mind:<\/p>\n\n\n\n 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.<\/p>\n\n\n\n For more details you can follow MySQL document. click here<\/a><\/p>\n\n\n\n Thanks for reading!!!<\/p>\n","protected":false},"excerpt":{"rendered":"Below are few tips you should take care in your SQL queries:-<\/h2>\n\n\n\n
1. Use<\/strong> SELECT column_name<\/mark><\/strong> fields instead of<\/strong> SELECT *<\/strong><\/mark><\/h3>\n\n\n\n
SELECT * FROM employee;<\/pre>\n\n\n\n
SELECT first_name, last_name FROM employee;<\/pre>\n\n\n\n
2.<\/strong> Avoid using<\/strong> SELECT DISTINCT<\/strong><\/mark><\/h3>\n\n\n\n
SELECT DISTINCT first_name, last_name FROM employee;<\/pre>\n\n\n\n
SELECT first_name, last_name, class, roll_no FROM employee; <\/pre>\n\n\n\n
3. Avoid running SQL queries in loop<\/strong><\/h3>\n\n\n\n
foreach ( $all_users as $user ) {\n $query = 'INSERT INTO users (first_name,last_name) VALUES (\"' . $user['first_name'] . '\", \"' . $user['last_name'] . '\")';\n}<\/pre>\n\n\n\n$user_data = array();\nforeach ( $all_user as $user ) {\n $user_data[] = '(\"' . $user['first_name'] . '\", \"'. $user['last_name'] . '\")';\n}\n$query = 'INSERT INTO users (first_name,last_name) VALUES' . implode(',', $user_data);<\/pre>\n\n\n\n4. Prefer SQL JOINS over Correlated sub-queries<\/strong><\/h3>\n\n\n\n
SELECT first_name, last_name FROM employee WHERE id IN (SELECT id FROM salary WHERE salary > 5000);<\/pre>\n\n\n\n
SELECT e.first_name, e.last_name FROM employee e JOIN salary s ON e.id = s.id WHERE s.salary > 5000;<\/pre>\n\n\n\n
5. Use EXISTS()<\/mark> rather than COUNT()<\/mark><\/strong><\/h3>\n\n\n\n
IF EXISTS<\/code> stops the processing of the select<\/code> query as soon as the first matching row is found, whereas SELECT COUNT(*)<\/code> continues searching until all matches are found.<\/p>\n\n\n\nIF (SELECT COUNT(first_name) FROM employee WHERE emp_id = 875) > 0<\/pre>\n\n\n\n
IF EXISTS(SELECT first_name FROM employee WHERE emp_id = 875)<\/pre>\n\n\n\n
6. Use Wildcards wisely<\/strong><\/h3>\n\n\n\n
SELECT first_name FROM employees WHERE first_name LIKE \u2018%mic%\u2019;<\/pre>\n\n\n\n
SELECT first_name FROM employees WHERE first_name LIKE 'mic%';<\/pre>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
\n