As soon as you have done your best to design the very best schema that you were able to do, it's time to specify and get the most out of your queries. There are a few commands, tips, hints, and pointers of which you should be informed in terms of query optimizations. However, as always, you should always adapt everything to your own needs because these are general guidelines that may or may not help in your unique case.

First, let's present the explain statement. This is a very useful command that can help you to decide which queries and/or indexes can improve/decrease performance. In a nutshell, the explain statement is synonymous with describe and can be used to gather and find out information regarding the way MySQL would execute a select statement. It returns the query execution plan information. And it can be used for optimization.

It is perhaps one of the most commonly used statements when optimizing queries. This command has lots of parameters and arguments, so please make sure that you check out the official documentation from MySQL Reference.

Moving on, priorities should be used to manipulate the jump queues or when you want to push further the execution of a specific command. Some of the most common scenarios are when you want to use select high_priority and even insert low_priority. In the aforementioned situations, the statements are executed on a higher priority; for example, the tables are selected earlier than the rest of the commands, and such.

Being frugal with disk I/O is also a great habit. How can you do that? Well, one way is by relying on insert delayed as many times as possible. Here we are referring to those situations where you don't need the data right away. The modifications are stored in the memory but not actually written on the disk. Later on, all of the changes are written at the same time without additional seeks or I/O activity-this happens when clients close the table.

From time to time you may run into situations where you are required to delete a lot of rows from a MySQL table. If so, then defragmenting the table with the statement optimize table may help a bit. After a somewhat large change to the table you can check how this command affects the performance of your queries (benchmark before and after, compare results, and see for yourself-there are no guarantees, though).

Simplify, simplify, simplify. Break down complex permissions into more but simpler ones. By doing so, permissions checking operating expense could be reduced. MySQL routinely checks after each grant command. Speaking of simplicity, please do eliminate unnecessary parentheses from queries (such as where). Visually, extra parentheses can help but they do add up (to only nano- or milliseconds each, but still).

Then again, try to experiment and play around with caches. Don't forget about the SQL_NO_CACHE command. This statement disables the automatic built-in query caching mechanism of MySQL. Explicitly giving hints regarding query caching can be of much more benefit if you know what you are doing; then, disabling the built-in feature is necessary to get the most out of your caching hints.

However, the above presumes that you already have activated the query caching function. We can't really take that for granted. But if you have no idea whether you have it enabled or not, then just read this excellent article published right on MySQL's official website. It covers everything you should know about its query caching function.

Finally, it's up to you to identify slow queries and do your best to research them in order to alleviate the problem. You should enable slow query logging of MySQL. Once it's enabled, you will be able to see which queries take too much time just be glancing over the log files. Extra data can also be found out by using such things as Lock_time, which describes the amount of time the query was waiting unnecessarily due to the lock(s).

As a final suggestion, please look into the mysqldumpslow Perl script that comes already bundled with MySQL. This nifty utility reports how often those slow queries run. This is important, because by knowing how many times a particular slow query that takes a specific amount of time runs, you will be able to decide the priorities according to which you will try to optimize the queries.

Ultimately, queries that produce a greater load (spikes) on the server, but do not necessarily take much time, but are run quite often, should also be optimized. With great loads, the server is overwhelmed and may not be as responsive as expected. Cache the queries; try memcache or caching tables. Oh and yes, cache objects, not results. The former can be re-used, while the latter cannot.