NYPHP Con: “Maximum Velocity MySQL” – Optimization and coding tips

With Jay Pipes of MySQL.

Jay Pipes is the Community Relations Manager, North America for MySQL, Inc. This was a three-hour tutorial about ways to fine-tune your MySQL queries.

This is part two of my notes. This post may contain inaccuracies, typos, boo-boos, etc. Jay says his slides will be available on his web site, so be sure to check there as well. If you have questions, direct them to Jay or consult the MySQL documentation.

Developing a database schema

• Use the smallest data type necessary
• Normalize your data; Denormalize only in extreme cases
• “Vertical partitioning”: Send to a different database based on the range of a key.
• Use ‘counter tables’ to mitigate query cache issues in InnoDB tables
• “Horizontal partitioning”: Split data into smaller tables. Put less-frequently accessed, less-frequently updated data in a separate table. Example: have separate tables for user logins and user addresses or interests.

Use indexes wisely

• Should have good selectivity (relative uniqueness of the index values to each other; there’s a way to calculate this)
• Look for ‘covering index’ opportunities
• On multi-column indexes, order is important
• As database grows, examine your index. Do you need to modify it?
• Remove redundant indexes for faster write performance. Don’t need more than one index on a field.

Coding tips

• Don’t think in a procedural programming style. Think about ‘sets’: you have two sets of data from which you need to retrieve data. How do you do this?
• Break queries into smaller units
• Use stored procedures (only available in MySQL 5.x)
• Learn to use JOINS effectively. Can often use a join instead of a subquery.
• When you use a function on an indexed function, the index will be ignored
• Use the ON DUPLICATE KEY UPDATE clause of the insert statement. No need to check first if the record exists then update or delete (Related: INSERT ON DUPLICATE KEY UPDATE and summary counters)
• TRUNCATE TABLE is typically faster than deleting records

Bulk loading performance

• Always use multi-record INSERT for mass bulk uploading
• Use ALTER TABLE … DISABLE KEYS syntax. It disables all indexes except for unique ones
• Use load data infile, or CSV storage engine (version 5.x only), then ALTER TABLE
• If loading into an InnoDB table, insert into a MyISAM table, then select from there into an InnoDB table
• To add or drop multiple indexes, use ALTER TABLE rather than multiple CREATE or DROP INDEX statements