SmartFAQ is developed by The SmartFactory (, a division of InBox Solutions (

How can I track down the cause of excessive database queries?
Requested by Dave_L and Answered by Mamba on 2007/1/19 9:55:07 (6748 reads)
--- Original details submitted by David_L on 2005/4/19 0:01:56 Here's a hack you can use to log all database queries, along with some information that identifies the user. You'll need to change "/example/path" to specify the path to a writable directory for the log files. This directory should be protected from viewing from a web browser. A new log file is created each hour. The log files may consume a lot of disk space, so monitor them. class/database/mysqldatabase.php (function queryF in class XoopsMySQLDatabase)
#*#DEBUG# - start
$logdir  '/example/path'### path to writable directory ###
$logext  'log'// log file extension
$now     time();
$ymdhms  date('Y-m-d H:i:s'$now);
$ymdh    date('Y-m-d-H'$now);
$sql_q   addcslashes($sql"rnt");
$logfile "$logdir/$ymdh.$logext";
error_log("[$ymdhms] [{$_SERVER["REQUEST_METHOD"]}] [{$_SERVER['REMOTE_ADDR']}] [{$_SERVER['HTTP_USER_AGENT']}] [{$_SERVER["SCRIPT_NAME"]}] [$sql_q]n"3$logfile);
#*#DEBUG# - end
$result =& mysql_query($sql$this->conn);
--- Additional details submitted by Madfish You can also find the number of database queries for page loads by turning on debug mode (in admin -> system -> preferences -> general). This will display the number of SQL queries at the bottom of each page, and you can click on the link to get a list of what all the queries were, which will help identify the problem areas. Turning them off and reloading the page will allow improvements (or not!) to be assessed.

  Print Q&A Send Q&A