Things that Kill MySQL Performance

1. Failing to plan for scaling out, scaling up
2. Not using EXPLAIN (learn from your mistakes)
3. Using the wrong data types (hint: use smallest fixed-size)
4. Using persistent connections in PHP (leads to zombie processes)
5. Using a heavy DB abstraction layer (hint: use PDO or custom)
6. Using the wrong storage engine (MEMORY, ARCHIVE, InnoDB, etc.)
7. Using indexes improperly (hint: select in the order of the index; hint: keep primary key small)
8. Issuing SQL queries that can’t be cached easily; having too large of a query cache
9. Using stored procedures when prepared statements are better
10. Using functions on indexed columns in the WHERE clause
11. Not indexing important columns; having redundant indexes
12. Using sub-queries when joins would be better
13. Issuing avoidable deep scans
14. Doing SELECT COUNT(*) without WHERE on InnoDB table.
15. Failing to profile/benchmark your SQL
16. Not using AUTO_INCREMENT if applicable
17. Not using ON DUPLICATE KEY UPDATE (1 query vs 2)

Reference:

• How to Kill MySQL Performance

Tags: mysql, optimize, pdo, performance, sql