"How to find the source of queries in MySQL Query Analyzer" or "SQL comments in Query Analyzer"

MySQL Enterprise Monitor offers a tool called "Query Analyzer" (QuAn). QuAn sits between any client app and the MySQL server and logs every query and its runtime statistics. A very cool tool for analyzing your SQL. More information is available here.

If you identify a query, that needs some improvement, sometimes it is hard to identify the source of that query as well. With hundreds of different PHP scripts for example it is not easy to know, which one issued the query, that you want to modify.

A good way to achieve this is adding C-style SQL comments. Let's look at an example:

SELECT * FROM mytable /*main.php*/;

Query Analyzer will strip that comment off before archiving the query. This is ok, because QuAn wants to consolidate all similar queries and this comment is irrelevant for the query.

But you can use version specific comments in MySQL. QuAn cannot ignore these comments,

because they may be relevant for query execution. And this is the solution for our problem:

SELECT * FROM mytable /*!99999 main.php*/;

This SQL comment will be executed on MySQL version 9.99.99 and later. But this version does not exist. So in reality the comment gets not executed at all. Currently we are at MySQL 5.1.42 so it will take some time before we reach MySQL 9.99.99 ;-)

The comment is in QuAn and will be logged. So you see the comment when monitoring the query and you know immediately, where that query came from.