Simple SQL & Database Performance Tips

SQL Tips
1. Use EXPLAIN or EXPLAIN EXTENDED to know the performance of your query

2. Don’t use DISTINCT when you have or could use GROUP BY

3. Try avoiding the use of LIKE if possible and if you will use LIKE in your query avoid wildcards at the start of LIKE queries

4. The use of LIMIT may not be as fast as it sounds

5. Know when to split a complex query and join smaller ones

6. Make similar queries consistent so cache is used

7. Don’t use COUNT * on Innodb tables for every search, do it a few times and/or summary tables, or if you really need it

8. Use UNION if your sure there are duplicates, if there are no duplicates then use UNION ALL because UNION by default performs the equivalent of a SELECT DISTINCT on the final result set

9. Try to avoid updating heavily indexed columns

10. Try to minimize the updating of tables that have UPDATE triggers

11. Try to avoid using Triggers if possible, incorporate the logic of trigger in stored procedure

12. Those operators at the top will produce results faster than those listed at the bottom.

1. =
2. >, >=, <, <=
3. LIKE
4. <>

13. When you have a choice of using the IN or the BETWEEN clauses choose BETWEEN clause, as it is much more efficient.

14. In your WHERE clause, put the least likely true AND expression first. This way, if the AND expression is false, the clause will end immediately, saving time.

15. In your WHERE clause, if both parts of an AND expression are equally likely being false, put the least complex AND expression first. This way, if it is false, less work will have to be done to evaluate the expression.

16. Keep the number of rows returned by the query as small as possible

17. Keep the number of groupings as few as possible

18. Don’t group redundant columns

19. Rewriting a query with OR conditions as a UNION

20. Use the WHERE clause instead of using HAVING clause, whenever possible

21. Use table variables instead of temporary tables

22. Try to use constraints instead of triggers, whenever possible.

23. Try to avoid using SQL Server cursors, whenever possible

24. Use views and stored procedures instead of heavy-duty queries

25. Try to restrict the queries result set by returning only the particular columns from the table, not all table’s columns

26.  Put ORDER BY NULL after GROUP BY for better performance, when you do not need GROUP BY results sorted

DATABASE Tips
1. Use indexes on strategic fields, not all fields.

2. Use Triggers wisely

3. Normalize Tables

This my collection of tips that I got from the net

LINKS:
Easy MySQL Performance Tweaks