How to log query and its execution time of each query which runs on a db?

mysql sql logging triggers crud
Techie picture Techie · Apr 23, 2013 · Viewed 12.6k times · Source

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 ?

Answer

kuldeep.kamboj picture kuldeep.kamboj · Apr 23, 2013

Use mysql slow query log. This will help you to get only queries which are slow instead of log / analyze all queries. You just need to set param for long_query_time like 1 or 2 second.

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More