MySQL Query Performance Tuning

1. SQL Performance Tuning team recommends using COUNT(1) instead COUNT(*) for MySQL query performance optimization.

Example:

--Do not use:
SELECT COUNT(*)
FROM mysql_tuning;

--Use:
SELECT COUNT(1)
FROM mysql_tuning;

2. Never compare NULL with NULL. Consider that NULL is not like an empty string or like the number 0. Also NULL can not be not equal to NULL (NULL != NULL).

Example:

SELECT COUNT(1)
FROM mysql_tuning
WHERE 1 = 1;

--Result: With condition 1 = 1 you will get result. But in this case you can also omit condition 1 = 1.

SELECT COUNT(1)
FROM mysql_tuning
WHERE NULL = NULL;

--Result: With condition NULL = NULL you will get result 0.

SELECT COUNT(1)
FROM mysql_tuning
WHERE NULL != NULL;

--Result: With condition NULL != NULL you will also get result 0.

3. If you are using more than one table, make sure to use table aliases.

Example:

SELECT COUNT(1)
FROM mysql_tuning t, mysql_format f
WHERE t.id = f.id;

4. It is good practice to use table column names in an MySQL query. This way the SQL statements will be more readable, but that is not the main reason. Example: If in INSERT statement you use SELECT * FROM x and at some point you add a new column in table x, SQL will return an error. The third reason why it is better to use table column names is to reduce network traffic.

5. In WHERE statements make sure to compare string with string and number with number, for optimal MySQL query performance.

Example:

Note: Column id is NUMBER Data Type.

--Do not use:
SELECT id, apn, charging_class
FROM master
WHERE id = '4343';

--Use:
SELECT id, apn, charging_class
FROM master
WHERE id = 4343;

6. Do not change column values in WHERE statements if possible, recommended by SQL Performance Tuning.

Example:

--Do not use:
WHERE CONCAT('(', a.mysql_query_field, ')') = CONCAT('(', b.mysql_query_field, ')')

--Use:
WHERE a.serial_id = b.serial_id

7. Avoid using complex expressions.

Examples:

--Avoid:
WHERE serial_id = SUBSTRING(a.mysql_tuning_field,5)

WHERE SUBSTRING(a.mysql_tuning_field,5) = ( 1, etc...)

8. If you need to use SQL functions on join predicates that is okay, but do not use them with indexed table columns.

9. EXISTS vs. IN for MySQL sub queries
If the selective predicate is in the sub query, then use IN. If the selective predicate is in the parent query, then use EXISTS.

10. SQL Performance Tuning recommends using CASE statements. It is more efficient to run a single MySQL statement, rather than two or more separate MySQL statements.

Example:

--Do not use:
SELECT COUNT (1)
  FROM mysql_tuning
 WHERE salary <= 1000;

SELECT COUNT (1)
  FROM mysql_tuning
 WHERE salary BETWEEN 1000 AND 2000;

--Use:
SELECT COUNT (CASE WHEN salary <= 1000
                   THEN 1 ELSE null END) count_1,
       COUNT (CASE WHEN salary BETWEEN 1001 AND 2000
                   THEN 1 ELSE null END) count_2
  FROM mysql_tuning;

11. Use UNION ALL instead of UNION, if possible

Example:

--Do not use:
SELECT id, name
  FROM mysql_tuning
 UNION
SELECT id, name
  FROM mysql_tuning_format

--Use:
SELECT id, name
  FROM mysql_tuning
 UNION ALL
SELECT id, name
  FROM mysql_tuning_format

12. SQL Performance Tuning recommends to use minimal number of sub queries in MySQL query, if possible.

Example:

--Do not use:
SELECT id, manufacturer, model
  FROM cars
 WHERE price = ( SELECT MAX(price)
                   FROM mysql_tuning
               )
   AND year =  ( SELECT MAX(year)
                   FROM mysql_tuning
               )

--Use:
SELECT id, manufacturer, model
  FROM cars
 WHERE (price, year) = ( SELECT MAX(price), MAX(year)
                           FROM mysql_tuning
                       )

13. SQL Performance Tuning recommends when cretin data are used frequently, it is a good idea to store them into intermediate tables.

15. SQL Performance Tuning OR vs. IN.
Our tests showed that using IN in WHERE condition is a little faster then using OR.

Example:

--Do not use:
SELECT *
  FROM CDRS_NR
 WHERE RECORD_TYPE = 'MTC'
    OR  RECORD_TYPE = 'MOC'
    OR  RECORD_TYPE = 'SMSO'

--Use:
SELECT *
 FROM CDRS_NR
WHERE RECORD_TYPE IN ('MTC', 'MOC', 'SMSO')