NYPHP Con: “Maximum Velocity MySQL” - Indexes and storage engines

With Jay Pipes of MySQL. Diversity stats, in case you’re wondering: Men: about 30. Women: 4. People of color: 4.

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 one 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.

Indexes

• Gives you a sorted order that allows you to find things quickly.
• Indexes speed up select or read statements.
• Indexes slow down writing to the database because MySQL not only has to write the record, but also has to write the index.

The kind of indexes available and their efficiency depends on the type of storage engine you choose. Keep your indexes slim on write-intensive applications.

Types of storage engines

Choose your table’s storage engine type based on the type of information it holds, and the function for which you will use the table. Can mix table types in the same database.

MyISAM

• Unclustered data storage
• Good insert performance and good with concurrent inserts
• Small foot print
• B-tree, r-tree and FULLTEXT indexing; Can use for spatial calculations, such as ZIP codes
• Poor concurrent update and delete performance
• No foreign key support; You can use a foreign key, but relational integrity is not guaranteed. (Forthcoming in version 5.2)
• No transactions available

Good for warehousing data, logging data and auditing data.

InnoDB

Good for when you need a lot of concurrent updates and will be looking data up by ID. Good for online transaction processing.

• NO FULLTEXT indexing or spacial data (r-tree indexes).
• Large memory usage and a large data footprint.
• Full transactional support
• Support for foreign keys
• Be sure to set a primary key, and use the smallest possible key (INT not BIGINT). If you don’t InnoDB will create it’s own super-integer index. Because of the way InnoDB handles indexes, this is not desirable.

Archive

Good for archiving, audit logging, and distributable media (for example, applications that you don’t want the user to be able to modify).

• Quick inserts and table scans
• Read-only
• Zlib compression
• No indexes allowed
• No foreign key support

Memory / HEAP

Great for temporary tables, session data, calculation tables, or information that will be requested frequently.

• Very, very fast selects because it’s all stored in memory
• Supports hash or b-tree indexes
• Data is gone on reboot, but you can get around this with init_file or my.cnf/my.ini.
• Limited by amount of memory available on the server

Can combine engine types within the database to take advantage of each storage engine’s strengths.