Only SELECT the fields you require
All to often web developers will SELECT * which selects all fields for a record. However, you’d be surprised how much of a strain SELECT * can cause when running a query, especially on larger tables. So, instead of selecting absolutely everything, just select the fields you need. it takes up less memory on your server and thus allowing your database server to use that free memory to handle other queries and processes.
Use EXPLAIN to analyze your query
This is probably one of the first recommendations you will run into when you are trying to optimize a MySQL query. EXPLAIN is a useful command in MySQL which can provide you some great details about how a query is ran, what index is used, how many rows it needs to check through and if it needs to do file sorts, temporary tables and other nasty things you want to avoid.
To use EXPLAIN, I firstly recommend downloading MySQL Workbench which will provide you with a query browser which you can run queries and see the results of them without sticking them in PHP,etc.
If you find that your query is using a file sort or temporary tables, you may be able to optimize your query and thus speed up it’s execution time.
Use COUNT(1) to count the number of records
This point slightly crosses over into PHP. In PHP there is a function called mysql_num_rows(). It’s purpose is to count the number of results returned by a query. While this sounds like a really good idea, my experience shows that this takes a whole lot longer than running a second COUNT(1) query on MySQL.
Apply indexes to useful fields
Indexes are like the letter tabs in a phone book. They make it quicker for you to find the data you need that match a certain criteria. Creating good indexes is an art and you need to think about how you will be querying your data. Things to consider are what fields are you going to use in your WHERE and how you are going to ORDER BY. When you have this information you will be able to create indexes that will speed up your queries instantly.
Use multiple inserts if possible
Do you have a lot of INSERT queries to run at once? Why not send them all to MySQL together. It will be executed a lot quicker. That can be especially useful when doing imports of data.
Use correct field types for the data
There are a lot of different field types available through MySQL, but I suggest getting familiar with the string and numeric types to get a better idea of which are best of your data. For instance, if you are only going to ever store the numbers 1, 2 and 3 in a field, use TINYINT rather than INTEGER. It takes up less space on your server.