Today we will learn a few interesting things about SQL Query Optimization.

How to get query execution time and executed query number?

Follow the code given below:

function timer()
{
$time = explode(‘ ‘, microtime());
return $time[0]+$time[1];
}
$beginning = timer();
$timer_queries += round(timer()-$beginning,6);


The sql query becomes faster if you use the actual columns names in SELECT statement instead of than ‘*’.
For Example: Write the query as
SELECT id, first_name, last_name, age, subject FROM student_details;
Instead of:
SELECT * FROM student_details;
HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as
SELECT subject, count(subject)
FROM student_details
WHERE subject != ‘Science’
AND subject != ‘Maths’
GROUP BY subject;
Instead of:
SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= ‘Vancouver’ AND subject!= ‘Toronto’;
Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as
SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = ‘Electronics’;
Instead of:
SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = ‘Electronics’;
Be careful while using conditions in WHERE clause.
For Example: Write the query as
SELECT id, first_name, age FROM student_details WHERE age > 10;
Instead of:
SELECT id, first_name, age FROM student_details WHERE age != 10;
Write the query as:
SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)
Instead of:
SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)

In SQL, wildcard is provided for us with ‘%’ symbol.

Using wildcard will definitely slow down your query especially for table that are really huge. We can optimize our query with wildcard by doing a postfix wildcard instead of pre or full wildcard.

  • #Full wildcard
  • SELECT * FROM TABLE WHERE COLUMN LIKE ‘%hello%’;
  • #Postfix wildcard
  • SELECT * FROM TABLE WHERE COLUMN LIKE ‘hello%’;
  • #Prefix wildcard 6 SELECT * FROM TABLE WHERE COLUMN LIKE ‘%hello’;

count vs exist

Some of us might use COUNT operator to determine whether a particular data exist 1. SELECT COLUMN FROM TABLE WHERE COUNT(COLUMN) > 0
Similarly, this is very bad query since count will search for all record exist on the table to determine the numeric value of field ‘COLUMN’. The better alternative will be to use the EXIST operator where it will stop once it found the first record. Hence, it exist.
Limit The Result
Another common way of optimizing your query is to minimize the number of row return. If a table have a few billion records and a search query without limitation will just break the database with a simple SQL query such as this.
1 SELECT * FROM TABLE
Hence, don’t be lazy and try to limit the result turn which is both efficient and can help minimize the damage of an SQL injection attack.
1 SELECT * FROM TABLE WHERE 1 LIMIT 10

In Subquery
Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.
For Example: Write the query as
Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)
Instead of:
Select * from product p
where product_id IN
(select product_id from order_items
Some of us will use a subquery within the IN operator such as this.
1 SELECT * FROM TABLE WHERE COLUMN IN (SELECT COLUMN FROM TABLE)
Doing this is very expensive because SQL query will evaluate the outer query first before proceed with the inner query. Instead we can use this instead.
1 SELECT * FROM TABLE, (SELECT COLUMN FROM TABLE) as dummytable WHERE dummytable.COLUMN = TABLE.COLUMN;
Using dummy table is better than using an IN operator to do a subquery. Alternative, an exist operator is also better.
Utilize Union instead of OR
Indexes lose their speed advantage when using them in OR-situations in MySQL at least. Hence, this will not be useful although indexes is being applied
1 SELECT * FROM TABLE WHERE COLUMN_A = ‘value’ OR COLUMN_B = ‘value’
On the other hand, using Union such as this will utilize Indexes.
1 SELECT * FROM TABLE WHERE COLUMN_A = ‘value’
2 UNION
3 SELECT * FROM TABLE WHERE COLUMN_B = ‘value’
Hence, run faster.

Latest posts by Sahel Aktar

  • Share/Bookmark