SQL Query Optimization Be careful while using conditions in WHERE clause

when you add condition in WHERE clause be careful some SQL clause like this ..


1. Use function in where clause ..index not use
use this query for better performance :
WHERE field_name LIKE 'test%';
instead of this query:
WHERE SUBSTR(field_name,1,4) = 'test';


2. Use BETWEEN better than use logical operator
use this query for better performance :
WHERE salary BETWEEN 10000 AND 50000
instead of this query:
WHERE salary >= 10000 AND salary<= 50000


3. Use calculation in WHERE clause made query performance to down
use this query for better performance :
WHERE fname='SOMSUK' AND  lname='KASEM'
instead of this query:
WHERE fname||lname='SOMSUKKASEM'

use this query for better performance :
WHERE salary =20000
instead of this query:
WHERE salary+5000 =25000