Mysql – How to properly debug and inspect SQL Queries in PHP File

1. This sounds like a perfect use of TRIGGERS to me. You say you have transactions, so you must already be using InnoDB. Attach triggers on INSERT, UPDATE, and DELETE on the tables you care about, each one triggering an INSERT into a log table AFTER the table event. There's your SELECT filter, done. You also have total control over what you log and how.

   This is a higher level solution, closer to the application layer, not a low-level system log solution where you have an exact copy of the complete SQL query. But I think it does address some of your concerns.

   InnoDB Triggers have access to stored procedures and all the MySQL functions, so I presume you can find the user's login name in there somewhere. The log time is just NOW().

   You may end up with a lot of triggers to manage, depending on your number of tables, but it's just a copy/paste job for each one. I maintain the code for all triggers in a single reference SQL file along with the table definitions and views for that database.

   I've never tested triggers in a transaction-rich environment to make sure the trigger only fired after the transaction was committed. The docs are thin on this subject, but they do say, "An AFTER trigger is executed only if the BEFORE trigger (if any) and the row operation both execute successfully." Sounds conclusive to me. Now you also know you're not logging things that didn't actually work.

2. OR set up replication, even to another mysqld instance on the same box if necessary. Turn on your mysqld full query logging on the slave instance. Failed transactions don't get replicated, and neither do SELECTs.

The slow query log gets updated once a query is completed and the query time is beyond or equal to value set in long_query_time.

So it could mean either good or bad. The good is all the queries completed within long_query_time. The bad is the slow query is still running.

To experience a slow query you can try subqueries with multiple level.