Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  Other Optimization Tips

8.2.5 Other Optimization Tips

This section lists a number of miscellaneous tips for improving query processing speed:

  • If your application makes several database requests to perform related updates, combining the statements into a stored routine can help performance. Similarly, if your application computes a single result based on several column values or large volumes of data, combining the computation into a UDF (user-defined function) can help performance. The resulting fast database operations are then available to be reused by other queries, applications, and even code written in different programming languages. See Section 20.2, “Using Stored Routines (Procedures and Functions)” and Section 24.4, “Adding New Functions to MySQL” for more information.

  • To fix any compression issues that occur with ARCHIVE tables, use OPTIMIZE TABLE. See Section 15.6, “The ARCHIVE Storage Engine”.

  • If possible, classify reports as live or as statistical, where data needed for statistical reports is created only from summary tables that are generated periodically from the live data.

  • If you have data that does not conform well to a rows-and-columns table structure, you can pack and store data into a BLOB column. In this case, you must provide code in your application to pack and unpack information, but this might save I/O operations to read and write the sets of related values.

  • With Web servers, store images and other binary assets as files, with the path name stored in the database rather than the file itself. Most Web servers are better at caching files than database contents, so using files is generally faster. (Although you must handle backups and storage issues yourself in this case.)

  • If you need really high speed, look at the low-level MySQL interfaces. For example, by accessing the MySQL InnoDB or MyISAM storage engine directly, you could get a substantial speed increase compared to using the SQL interface.

  • Replication can provide a performance benefit for some operations. You can distribute client retrievals among replication servers to split up the load. To avoid slowing down the master while making backups, you can make backups using a slave server. See Chapter 17, Replication.

Download this Manual
User Comments
  Posted by Mark Kozlowsky on January 15, 2010
I've been using MySQL 5.0.85-Community for a while now on Debian Linux. The MySQL package that is installed with Debian by default includes the InnoDB engine, but in most cases, you wouldn't need InnoDB and disabling the storage engine can save you a lot of memory optimizing your overall database performance when RAM size is a constraint.

For example on the host I've been running using Debian 5.0 Lenny and MySQL 5.0.85-Community, the InnoDB engine took around 100MB of memory even at idle times. Wasting 100 of RAM on a feature that is not used at all would cause major slowdowns if your machine has little RAM installed. So, If you don't need the InnoDB engine enabled, it's recommended to turn it off, to free up some memory and get extra optimization on machines with low memory.

To disable to InnoDB storage engine edit your my.cnf configuration file (usually in /etc/mysql/) and add the following line to it:


Save and close the file, restart your database server and you're done. You can also re-compile the package from scratch, removing the InnoDB storage engine completely, but I don't recommend this just in case you need to re-enable InnoDB for some reason in the future.
Sign Up Login You must be logged in to post a comment.