MySQL is a widely used and fast SQL database server. It is a client/server implementation that consists of a server daemon (mysqld) and many different client programs/libraries.

You can check the same tips fromhere.Here is very useful tips for all mysql DBA’s,Developers these tips are noted from MySQL Camp 2006 suggested by mysql community experts.

10 thoughts on “Top 84 MySQL Performance Tips”

1. Its a totally helpfull material

2. Thanks for a good list. Here are a few more tips:

   -Use as short indexes as possible. Use smallint if you don’t need a whole int. When indexing hex data (eg md5 hash), you can store it as two BIGINTs.
   -Delete data that you don’t need. Tables will grow, so monitor table sizes regularly. Use partitioning (or archive, or merge tables as mentioned above).
   -Use SHOW PROFILES to get more details than EXPLAIN
   -Monitor the heaviest queries, for example using Jet Profiler for MySQL

3. “37. Use a clever key and ORDER BY instead of MAX”

   what is a ‘clever key’?

   is this just a column with a ‘1’ to flag the max?

4. Thanks. This is exactly what I was looking for.

5. Hands down, the best way to improve MySQL performance is PHP data caching. What I normally do is cache the data results from a MySQL query into a table called ‘cache’ that establishes a primary key associated with that specific data. If a user accesses that data, and the cache time hasn’t expired, I grab the data from the cache which is easily 2000 faster. (primary key lookups are a cinch)

6. Other tips:
   – think about persistent connections
   – use the right MySQL connector (e.g. PHP’s mysqli is far better than the older mysql)

7. Really helpful tips. Thanks.

8. > Use Slow Query Log (always have it on!)

   for what? use Slow Query Log for develop

   >Don’t use COUNT * on Innodb tables for every search, do it a few times and/or summary tables, or if you need it for the total # of rows, use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS()

   Do not use SQL_CALC_FOUND_ROWS too, because it is slow (no much faster than COUNT).

   > pare down cron scripts
   you can solve some task with cron scripts, but at this time load increasing at server, its no good really

   > Don’t use ORDER BY RAND() if you have > ~2K records
   do not use ORDER BY RAND() never. You know range ID’s (last auto_increment, for ex.) and generate random value from script

9. Hi there, thanks for the helpful list.

   If I may just ask about point no. 24: “separate text/blobs from metadata, don’t put text/blobs in results if you don’t need them”.

   How exactly is this division achieved?
   Do you have two tables (or two databases?) one holding blobs, and the other, metadata (with a foreign key)?
   Is this a secure and reliable strategy?
   Are there certain things I need be warned of?

   Thanks for your time.

10. Hi John,how do i do caching using php?