When creating a table, ask yourself what types of queries you'll use it for. Then choose a storage engine that uses a locking level appropriate for the anticipated query mix. MyISAM table-level locking works best for a query mix that is heavily skewed toward retrievals and includes few updates. Use InnoDB if you must process a query mix containing many updates. InnoDB's use of row-level locking and multi-versioning provides good concurrency for a mix of retrievals and updates. One query can update rows while other queries read or update different rows of the table. Additional discussion of locking issues is given in Chapter 28, "Locking." If you're using MyISAM tables, choose their structure to reflect whether you consider efficiency of processing speed or disk usage to be more important. Different MyISAM storage formats have different performance characteristics. This influences whether you choose fixed-length or variable-length columns to store string data:
Another option with MyISAM tables is to use compressed read-only tables. For InnoDB tables, it is also true that CHAR columns take more space on average than VARCHAR. But there is no retrieval speed advantage for InnoDB as there is with MyISAM, because the InnoDB engine implements storage for both CHAR and VARCHAR in a similar way. In fact, retrieval of CHAR values might be slower because on average they require more information to be read from disk. MERGE tables can use a mix of compressed and uncompressed tables. This can be useful for time-based records. For example, if you log records each year to a different log file, you can use an uncompressed log table for the current year so that you can update it, but compress the tables for past years to save space. If you then create a MERGE table from the collection, you can easily run queries that search all tables together. More information about storage engine-specific optimizations is given in Chapter 38, "Optimizing Databases." |