MySQL databases run pretty well out of the box and you may not need to do any tuning at all. We don't recommend tweaking for tweaking sake. MySQL distribution contains bunch of sample my.cnf files which may be great templates to use. Typically they would already be much better than defaults if you chose correct one. Described below are some techniques you can use to improve your MySQL database performance. Generally speaking these are written from the point of few of a hosting company - MySQL servers are generally either standalone of share the server with a web server. Typically a MySQL will be hosting multiple database with multiple exclusive users.
Download and run MySQLTuner, and excellent free Perl script. You'll need root access to the server to run this script, and your MySQL servers administrative (root) login. The results of the script provide a variety of test results, and some recommendations. See screenshot below.
It's advisable to make a backup of your MySQL CNF file before making changes. Open your my.cnf file in a text editor, make any of the changes you decide to try, then restart MySQL. As some of the tests work best when MySQL has been running for a while, it's best to retest after a period of time has passed - how much will depend on how busy your server is, but 24 hours should be enough for a reasonable active database server.
One of the recommendations frequently seen in MySQL Tuner is that you should OPTIMIZE TABLES. The goal of optimising is to defragment tables for better performance. There are number of ways to do this, and they don't all require MySQL root access.
From the command line run this command:
mysqlcheck --optimize -A -u username -ppassword
mysqlcheck is a useful utility for checking, repairing, anaylsing and optimising MySQL tables. The -A option means all databases, and the --optimize option reclaims any unused space and defragments the data file.
This is for users who don't have direct access. Use your normal MySQL administration tool, be this a web based tool such as PHPMyAdmin, or a standalone desktop program such as Navicat or SQLyog. Select a particular table, and look for the Optimise option either in the context (right click) menu, or as part of the web administration page.