Some thoughts on MySQL optimization

First, the BENCHMARK function. It can be used to run a command a number of times.

e.g. SELECT BENCHMARK(1000, "SELECT COUNT(*) FROM categories")

The EXPLAIN keyword, followed by a SELECT query, gives info for that query. I have to dig more into this.

Analyzing your tables seems to be very important. It re-creates keys, indexes or something like this. Not sure, but someone tells that it improved his queries 1000 times. Well,
mysqlcheck -Aa -uroot -p does it.

Use persistent connections to avoid connection overhead.

Make sure all your foreign keys are indexed.

Make sure you foreign keys match the type and size of the main keys.

Use the smallest data type/data type size possible.