when working with big data, you probably faced issue to fetch number of records from database when number of records are more than 1,00,000. Suppose your developed web application which have backend mysql database is running slow and you wish to optimise web application to find out database queries which are executing slow. So, you can optimise slow queries and speedup application. Now, Testing each database query is bit time consuming task. Think about something helps you to list out all the queries at one place which takes more time than your specified time. Yes, In this post we will set configuration that lists all the queries running slow automatically in one file when you test the project.
First of all set below parameters in mysql configuration file. If you are using Linux operating system than default path if configuration file should be ‘/etc/mysql/my.cnf’ or find mysql config file in linux operating system. If you are using XAMPP than default path of configuration file is
[XAMPP Directory]/mysql/bin/my.ini. Suppose, You have installed in
C: drive than path would be
Open configuration file and add below parameters under
slow_query_log = 1
slow_query_log is used to enable or disable query log option.
log-queries-not-using-indexes tells MySQL to log any queries that do not use indexes.
long_query_time=10 indicates that queries need to be logged that took more than 10 seconds to execute
log-slow-queries logs all slow queries to
That’s it. MySql Configuration is updated. Now, Create log file on particular location which you have set in configuration file. For Linux OS, Create directory using
mkdir command if directory does not exists and than create file using
If you are using Linux OS than you need to add permission to log file that you have set in mysql config.
chown mysql:mysql -R /var/log/mysql
Now, restart mysql service to apply changes.
mysql service start testing application and mysql will log all slow queries which took more than 10 seconds to execute.
For, further help you may comment or send your query on email@example.com as well.