1. Query cache – On most servers, there are certain MySQL queries that you and/or your scripts will run more often than others. In fact, you may run the same query hundreds or even thousands of times in a single day. The query_cache setting will save the most used queries in memory for quick access
query_cache_size=32M (32MB for every 1GB of RAM)
2. Key buffer size – This refers to the size of buffers used for indexes. A larger buffer will result in faster SQL response time when a command is issued. The key_buffer_size should be at least 1/4 but no more than 1/2 of the RAM size of the server.
key_buffer=256M (128MB for every 1GB of RAM)
3. Table cache – Just like MySQL can cache queries, it can also cache tables. If you think about how often a database table would be accessed, especially when using content management systems and web applications, this only makes sense. You can adjust this according to your memory.
Next week, we will cover more MySQL optimization tips.