performance_tuning.md

Performance Tuning

Table of Contents

• Avoid
  • WHERE

Avoid

WHERE

Avoid using the below operators in a WHERE clause due to index search is not working.

1. NOT IN
   Case: use NOT IN

   -- DON'T
   SELECT name, id
   FROM customer
   WHERE status NOT IN ('3');

   -- DO
   SELECT name, id
   FROM customer
   WHERE satus IN ('0', '1', '2', '4');

   Recommend to use:

   • IN
   • EXISTS, NOT EXISTS [CHECK]

2. <>, !=
   Case: use not equal operator (<>, !=)

   -- DON'T
   SELECT *
   FROM user
   WHERE gender != 0;

   -- DO
   SELECT *
   FROM user
   WHERE gender > 0;

   Recommend to use:

   • =
   • >, >=,
   • <, <=

3. NOT BETWEEN
   Case: ranges with NOT BETWEEN

   -- DON'T
   SELECT name, publisher, price
   FROM book
   WHERE price NOT BETWEEN 0 AND 149;

   -- DO
   SELECT name, publisher, price
   FROM book
   WHERE price BETWEEN 150 AND 300;

   Recommend to use:

   • BETWEEN

4. NULL
   Case: compare to NULL

   SELECT id, name, class
   FROM credit
   WHERE score IS NOT NULL;

   SELECT id, name, class
   FROM credit
   WHERE score > ``;

5. LIKE %x%
   Case: starts with %

   -- DON'T
   SELECT area_code
   FROM location
   WHERE city LIKE '%San%';

   -- DO
   SELECT area_code
   FROM location
   WHERE city LIKE 'San%';

   Recommend to use:

   • x%

6. OR
   Case: use OR

   Recommend to use:

   • UNION ALL

7. Converting values