SQL Optimization Tips

SQL Optimization Tips: –

Some techniques to write optimized queries in SQL which can make your life easy ! –

1) The SQL queries become faster if you select specific column names instead of using “ * ”.

Eg: –
Write query like –> select col1, col2 from table

Instead of –> select * from table

2) Having clause is used to filter data from the SQL query result set while Where clause is used to filter data from database table, don’t use it for any other purpose

Eg: –
Write query like –> select name, city from table where city = “jaipur” and city = “alwar” group by city

Instead of –> select name, city from table group by city having city = “jaipur” and city = “alwar”

Try to avoid multiple columns in group by clause
Write query like –> select name, city, state from table where city = “gandhinagar” and state = “rajasthan” group by state

Instead of –> select name, city, state from table where city = “gandhinagar” group by city, state having state = “rajasthan”

3) Try to use Union clause instead of Joins(if possible)

Eg: – if you want distinct id from 2 tables having same column names
Write query like –> select name, city from table1
union select name, city from table2

Instead of –> select name, city from table1 inner join table2 on table1.name = table2.name and table1.city = table2.city

4) Avoid the use if IN or NOT IN in your SQL statement: – you can use joins instead of IN or NOT IN because IN or NOT IN has slow performance

Eg: –
Write query like –> select * from table inner join table_city on table.city = table_city.city

Intead of –> select * from table where city in (select city from table_city)

5) Optimize Joins: –

Eg: –
Write query like–> select table1.id, table1.name, table1.city from table1 inner join (select id from table2 where table2.city = “jaipur”) as tab2 on table1.id = tab2.id

Instead of –>select table1.id, table1.name, table1.city from table1 inner join table2 on table1.id = table2.id where table2.city = “jaipur”

Reason –> first query has less number of rows to join than second query, while both queries gives same resultset.

6) Use Order by clause: – use order by clause in your SQL query because without order by clause every execution of sql query results in different sequence of resultset.

7) Try >= instead of > : – If there is an index on column

Eg :-
Try –> select * from table where column >= 4

Instead of –> select * from table where column > 3

Instead of looking in the index for the first row with column = 3 and then scanning forward for the first value that is > 3, the DBMS may jump directly to the first entry that is = 4.

8) Try Union instead of Or: The database may do a better job of optimizing two selects connected via union rather than one select with an or operator. For example,

Eg : –
Try –> select * from a, b where a.col1 = b.col1
union
select * from a, b where a.col2 = b.col2

Instead of –> select * from a, b where a.col1 = b.col1 or a.col2 = b.col2

Gaurav Mittal