Knowledge Base

Mysql Optimization techniques

Mysql optimization is an important phase which makes sure that your database server will handle large volume of data as well as can handle vast queries with more efficiency. Below are the few tips and tricks which help you to provide faster response and allow you to use complex mysql queries.

i) Check you slow query logs: Slow Query log file gives you all the queries which are taking much time to get executed on the server. So it’s requested that always keep your eyes on slow query log file to find out the problem area from mysql perspective. You can see the location of query log file by seeing my.cnf file as variable

log-slow-queries = /var/log/mysql/mysql-slow.log #location of your slow query log

ii) Check your mysql Cache size: Mysql cache size is the memory which is allowed from your server for Mysql queries to be executed. When any mysql query gets executed then it will use the cached memory to process all the data which are required by your query. So if you are running complex and large queries and your cache memory has assigned less memory then still you will get poor response from your server.

Below are the settings which are available in my.cnf file.

query_cache_size

query_cache_limit

iii) Query Optimization: Make your sql queries optimized so that it take minimum time to get executed. Query optimization is always a great way to handle large traffic on your website.

iv) Index Optimization: Use Index for those fields on which maximum search is performed. Index optimization is also an essential requirement for huge databases.