I'm looking for a succinct list of common pitfalls and optimizations for tuning a MySQL server as used for mid-size websites.
In general, the type of advice I'm looking for here is information that an ordinary developer or admin can easily implement that will give a measurable benefit to the performance of his site.
As an example
Here's a tip I picked up from reading High Performance MySQL that I frequently see use for:
When using the MyISAM storage engine (the default), the server will lock the entire table when performing DELETE or UPDATE operation, or when performing an INSERT which does not get appended to the end of the table (because there's a "hole" left from a previous DELETE). No other queries can use that table until the operation has finished.
Therefore, you should use InnoDB or other row-level-locking engines on any heavily-used table if it sees a lot of modification using any operation other than "INSERT".