Some MySql perfomance tips from greate “How to Kill Mysql Performance” presentation
- Use Explain
- Use the smallest data type possible
- Store IP addresses as INT UNSIGNED (The INET_ATON & INET_NTOA functions will help you)
- Persistent connection is evil
- Heavy abstraction layer is evil
- Learn both the benefits and drawbacks of each table engine (slide 21)
- Clustered layout is faster for lookup by primary key operations (than Non-clustered layout) (slide 26)
- If you don't pick a primary key, one will be created for you automatically
- Understand query cache (slide 32)
- Don't use stored procedures (slide 34)
- Be aware of deep scan slowdowns
- Be aware of COUNT(*) in InnoDB table
- Use ON DUPLICATE KEY UPDATE