Even the simplest sites with low traffic numbers eventually need to optimize their MySQL tables for superior performance. The problem is that plug-and-play Content Management Systems like WordPress and Drupal often make back-end MySQL optimization a mystery to novice and even above-average webmasters. If you want to configure your site’s MySQL tables for the best possible results, these simple yet highly productive tips can make it happen.

Shrink the DB When Possible

It goes without saying that a smaller database is easier to query and will result in faster searches. There are a couple of solid ways to cut back on database size. For starters, try deleting unnecessary meta data and orphan tables either automatically or manually on a schedule. Use a plugin designed for your CMS that drops unused tables to avoid unnecessary database growth.

Don’t Loop Your Queries

One of the biggest problems that face PHP-based sites reliant on MySQL is the so-called “loop query” fiasco. Simply put, it’s a situation where a for each construct sets off a chain of events that unduly taxes the MySQL engine. To avoid such a scenario, use MySQL’s UPDATE command to prevent loops that spiral off into infinity and end in relentless database queries.

Check Your Query Log Often

Looking at log files is just sound server management regardless of what you’re doing. MySQL log files can be particularly illuminating if you want to optimize server performance. What you’re looking for is the most common queries being performed in any given day, week or month. Once you know what’s common, you can optimize for those queries in a way that matters.

Enable a Caching Engine

It’s a waste of server resources to perform a lookup if the exact same lookup was performed not that long ago. That’s why caching platforms are so popular. Implementing a caching scheme is pretty easy thanks to the magic of plugins. Memcached, AdoDB and CSQL are great ways to make popular queries seem like calls for static pages and save resources.

Use Wildcards with Caution

As great as wildcards can be when crafting MySQL queries, they can result in unintended consequences if used haphazardly. Prefix and postfix wildcards are superior to general wildcards in most situations since they won’t result in a large number of database queries. The main point to take away is this: don’t rely on wildcards unless you’re fully aware of the ramifications.

Take Advantage of LIMIT

Under ideal circumstances, a query should only return a certain number of results. When site users are looking for a specific blog post or product on your site, the first ten or twenty will usually suffice. After that, you’re just wasting database calls on results that won’t matter to the typical user. Use LIMIT to keep results under 12 or so.

Get Rid of Query Caches

As you know, a cache is an important part of any computation optimization scheme. The problem is that caches can grow far faster than you’d think if you don’t set strict limits on them. Setting the query_cache_size equal to 0 will ensure that your MySQL tables don’t become a major drag on server resources regardless of your hosting configuration.

A Little Digging Goes a Long Way

Obviously, these tips are a bit general and their ultimate effectiveness depends on your site and server setup. Regardless, they’ll more or less work for any MySQL database and its associated tables. To get the most from these simple pointers, you’ll need to do a little more homework. Rest assured that the minimal legwork will yield dividends.