The performance of any Web application is greatly impacted by how well the database application is being used (if being used, of course). The four biggest impacts on performance are:
- The storage engine(s) used
- How columns are defined
- The existence of proper indexes
- How queries are written and executed
MySQL supports many different storage engines, each with its own strengths and weaknesses. Which you choose isn’t just a performance issue, it’s also one of features, like whether you need to support FULLTEXT searches (and therefore MyISAM tables) or transactions (which means InnoDB). Sometimes you can get away with using HEAP tables, which exist only in memory. There’s no permanent storage with HEAP tables, but the performance can’t be beat.
For the column definitions, the first goal is to use the smallest possible size for each column. This will be a limitation in your application, so the column’s size still needs to meet or exceed the largest possible value to be stored, but, generally speaking, the more data you want to possibly store, the more space will be required for all the data stored. You should also avoid allowing NULL values in columns. This is a factor in both good database design and in performance optimization.
The indexes you establish, and how they are used by queries, is a serious subject that requires a book in itself. To start, make sure you read MySQL’s own guide to how MySQL uses indexes. Second, learn to run your queries using EXPLAIN to see what, exactly, MySQL is doing when it executes that query. Also, of course, only select the data your application will actually need. No need to retrieve information that won’t be used. And always go back and double-check that your queries need all the clauses and references they’ve got. I’ve been known to leave legacy conditionals in queries that have no effect whatsoever.
For more on some of these topics, see the MySQL manual, of course (which has a section on optimization), the PlanetMySQL blog, and this article at DatabaseJournal.com. Also, if it applies to you, you can tweak MySQL’s behavior from a hardware/OS perspective. For an introduction to this subject, with respect to Linux, check out this article by Jeremy Zawodny, an expert on MySQL performance tuning.
