Menu
I have a huge database with more than 250 tables. Different type of queries are ran on the database. Since the database has grown over the years and now I need to optimise the database and queries. I have already followed optimisation concepts such as indexing and so on.
My problem is, How to log the query and its execution time of each query which runs on the database ? So I can analyse which query takes how many seconds and optimise them.
Given that I know that MYSQL Trigger would be ideal for this but I don't know how to write a trigger for the whole database, so that it logs each query to a table with query's execution time. I want the trigger to log all the CRUD operation which occurred in the database.
How can I get it done ?
325
Using Client StatisticsGo to Menu >> Query >> Select Include client Statistics. Execute your query. In the results panel, you can see a new tab Client Statistics. Go to the Client Statistics tab to see the execution time.
In the Execution Times section of the SET STATISTICS TIME ON statement output, we can find out the time taken by the SQL server to complete the execution of the compiled query plan. The CPU time indicates the total time that is spent by the CPU(s).
Now, go to the Scalyr dashboard menu and select MySQL. You will be able to see the log details of your MySQL, which includes the query time. This is a very simple and easy way to measure query time for a large number of MySQL queries.
Also you could set long_query_time = 0 and you will see all sql queries!
113
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
