MySQL Query Performance Tuning

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

Example:

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:

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

Example:

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.

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

Example:

7. Avoid using complex expressions.

Examples:

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:

11. Use UNION ALL instead of UNION, if possible

Example:

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

Example:

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: