What is the easiest way to see the MySQL queries run by a CiviCRM page?

Question

When debugging CiviCRM pages, is there a native way to emit the SQL statements being run, eg on a Search page? I'm looking for something easier than finding the code for the page and putting in a debug_var kind of statement. I'd prefer to not have to rely on a Drupal module like cTools, since we work with other CMSes frequently these days.

Answer 1

If you want to see everything, you can enable MySQL logging as follows (don't do this on production!):

1. From the mysql console (or within an application like MySQL Workbench), run:

   SET global general_log = 1;
   SET global log_output = 'table';
   

2. Look at all queries run within the last 15 seconds

   select * 
   from mysql.general_log 
   where 
     command_type = 'Query' and 
     event_time > now() - interval 15 second;
   

3. Try to find queries on the civicrm_contact table

   select * from mysql.general_log 
   where 
     argument like '%civicrm_contact%' and 
     argument not like '%general_log%' 
   order by event_time desc;
   

4. Clear the log

   truncate table mysql.general_log; 
   

5. Turn off logging

   SET global general_log = 0; 
   

Answer 2

Try setting:

define('CIVICRM_DAO_DEBUG', 1);

in your civicrm.settings.php file, this might work based on a quick glance at the code.

The output will appear onscreen and in the CiviCRM log file (eg sites/default/files/civicrm/ConfigAndLog/CiviCRM*log or media/civicrm/ConfigAndLog/CiviCRM*log or wp-content/plugins/files/civicrm/ConfigAndLog/CiviCRM*log)

Answer 3

If your page happens to be a report, you can install the Extended Reports extension which will display the full sql query being run.

Answer 4

I am not sure wether such a functionality exist in CiviCRM but something wich might be worth a look at is the logfiles