Modified DB function to include SQL EXPLAIN

Involved Joined: Jul 03, 2006 Posts: 273

Hi,

First off I'd like to say Great Job on the latest RN 2.3!

I have been having issues with unoptimized queries on some of my sites (not all running RN 2.3 but was intending to convert at least some of them) and as such I wanted a way to evaluate performance optimization.

So what I did was modified the mysql.php (in the db folder) to compliment the query loggin functions you've built in there.

I made a couple changes.

1. I changed the log file for SQL errors to be dberrorlog (instead of dblog which still exists for logging all queries)

2. I created a routine to check if the query is a select statement (when logging is on of course) and if so to print to yet another logfile (called dbexplainlog) a formated output of an EXPLAIN on the statement. This can be used for performance optimization and checking indexing etc.

Sample Output in foile looks like this:

Code:




December 1, 2008, 7:45 pm 


SQL was: SELECT * FROM nuke_users WHERE username='Duck' AND user_password=#####################'


Explain Results = Resource id #567


--------BEGIN Explain Results---------


ID = 1


SELECT TYPE = SIMPLE


TABLE = nuke_users


TYPE = ref


POSSIBLE KEYS = uname


KEY = uname


KEY LENGTH = 27


REF = const


ROWS = 1


EXTRA = Using where





--------END OF Explain Results---------

I added a config Variable to rnconfig.php called $logexplain which needs to be set to true (loglevel must be set on as well of course).

Anyway the code I am sure could use many improvements but nonetheless it seems to work well for me and is proving to be quite useful in tracking down where I might need to analyze some queries to either improve the queries themselves or the indexing of some tables.

I thought I might share my changes with you in case you'd like to include them in your system.

Note that this should only be used for debugging and you should clean up your log file when done. This will slow page load times down.

anyway here is the new mysql.php file with my changes (don't forget to add the $logexplain vairable when using set to true when not set to false in rnconfig.php:

Code:







/***************************************************************************


 *                                 mysql.php


 *                            -------------------


 *   begin                : Saturday, Feb 13, 2001


 *   copyright            : (C) 2001 The phpBB Group


 *   email                : support@phpbb.com


 *


 *   $Id: mysql.php,v 1.16.2.1 2005/09/18 16:17:20 acydburn Exp $


 *


 ***************************************************************************/





/***************************************************************************


 *


 *   This program is free software; you can redistribute it and/or modify


 *   it under the terms of the GNU General Public License as published by


 *   the Free Software Foundation; either version 2 of the License, or


 *   (at your option) any later version.


 *


 ***************************************************************************/





if(!defined('SQL_LAYER'))


{





define('SQL_LAYER','mysql');





class sql_db


{





   var $db_connect_id;


   var $query_result;


   var $row = array();


   var $rowset = array();


   var $num_queries = 0;


   var $isSelect;


   var $explain_result;


   //


   // Constructor


   //


   function sql_db($sqlserver, $sqluser, $sqlpassword, $database, $persistency = true)


   {





      $this->persistency = $persistency;


      $this->user = $sqluser;


      $this->password = $sqlpassword;


      $this->server = $sqlserver;


      $this->dbname = $database;





      if($this->persistency)


      {


         $this->db_connect_id = @mysql_pconnect($this->server, $this->user, $this->password);


      }


      else


      {


         $this->db_connect_id = @mysql_connect($this->server, $this->user, $this->password);


      }


      if($this->db_connect_id)


      {


         if($database != '')


         {


            $this->dbname = $database;


            $dbselect = @mysql_select_db($this->dbname);


            if(!$dbselect)


            {


               @mysql_close($this->db_connect_id);


               $this->db_connect_id = $dbselect;


            }


         }


         return $this->db_connect_id;


      }


      else


      {


         return false;


      }


   }





   //


   // Other base methods


   //


   function sql_close()


   {


      if($this->db_connect_id)


      {


         if($this->query_result)


         {


            @mysql_free_result($this->query_result);


         }


         $result = @mysql_close($this->db_connect_id);


         return $result;


      }


      else


      {


         return false;


      }


   }





   //


   // Base query method


   //


   function sql_query($query = '', $transaction = FALSE)


   {


      global $loglevel, $querycount, $logexplain;


   // Remove any pre-existing queries


      unset($this->query_result);


      unset($this->explain_result);


      if($query != '')


         {


         $this->query_result = @mysql_query($query, $this->db_connect_id);


         }


         if ($loglevel == 2) {


            if ($logexplain == true) {


               $this->isSelect = strtolower(substr(preg_replace('/\s+/', ' ', trim($query)), 0, 6));


               if ($this->isSelect == 'select'){


                  $explain_query = 'EXPLAIN '.$query;


                  $this->explain_result = @mysql_query($explain_query, $this->db_connect_id);


                  $explainlabels = array('ID = ','SELECT TYPE = ', 'TABLE = ', 'TYPE = ', 'POSSIBLE KEYS = ', 'KEY = ', 'KEY LENGTH = ', 'REF = ', 'ROWS = ', 'EXTRA = ') ;


                  $fplog = fopen(NUKE_BASE_DIR.'rnlogs/dbexplainlog','a');


                  $logvar = date("F j, Y, g:i a") . ' ' ."\n";


                  $logvar .= 'SQL was: ' . preg_replace('/\s+/', ' ', trim($query)) . "\n";


                  $logvar .= 'Explain Results = ' . $this->explain_result . "\n";


                  $logvar .= '--------BEGIN Explain Results---------'."\n";


                  while( $row = @mysql_fetch_array( $this->explain_result ) ) {


                        extract( $row );


                        for ($x = 0; $x <= 9; $x++){





                        $logvar .= $explainlabels[$x].$row[$x]. "\n";


                        }


                        }


                  $logvar .=  "\n";


                  $logvar .= '--------END OF Explain Results---------'."\n";


                  $logvar .= ' '."\n\n";


                  fwrite($fplog, "$logvar" . "\n");


                  fclose($fplog);


               }


            }


            $querycount = $querycount + 1;


            $fplog = fopen(NUKE_BASE_DIR.'rnlogs/dblog','a');


            $logvar = date("F j, Y, g:i a") . ' ' ;


            $logvar .= 'SQL was: ' . preg_replace('/\s+/', ' ', trim($query)) . "\n";


            $logvar .= 'querycount = ' . $querycount . "\n";


            fwrite($fplog, "$logvar" . "\n");


            fclose($fplog);





         }


      if($this->query_result)


      {


         unset($this->row[$this->query_result]);


         unset($this->rowset[$this->query_result]);


         return $this->query_result;


      }


      else


         {


            if ($loglevel > 0) {


               $error = $this->sql_error($query);


               $fplog = fopen(NUKE_BASE_DIR.'rnlogs/dberrorlog','a');


               $logvar = date("F j, Y, g:i a") . ' ' ;


               $logvar .= $error['code'] . ' : ' .  $error['message'] . "\n";


               $logvar .= 'SQL was: ' . preg_replace('/\s+/', ' ', trim($query)) . "\n";


               $logvar .= ' remote addr: ' . $_SERVER['REMOTE_ADDR'];


               fwrite($fplog, "$logvar" . "\n");


               fclose($fplog);


            }


         return ( $transaction == END_TRANSACTION ) ? true : false;


      }


   }





   //


   // Other query methods


   //


   function sql_numrows($query_id = 0)


   {


      if(!$query_id)


      {


         $query_id = $this->query_result;


      }


      if($query_id)


      {


         $result = @mysql_num_rows($query_id);


         return $result;


      }


      else


      {


         return false;


      }


   }


   function sql_affectedrows()


   {


      if($this->db_connect_id)


      {


         $result = @mysql_affected_rows($this->db_connect_id);


         return $result;


      }


      else


      {


         return false;


      }


   }


   function sql_numfields($query_id = 0)


   {


      if(!$query_id)


      {


         $query_id = $this->query_result;


      }


      if($query_id)


      {


         $result = @mysql_num_fields($query_id);


         return $result;


      }


      else


      {


         return false;


      }


   }


   function sql_fieldname($offset, $query_id = 0)


   {


      if(!$query_id)


      {


         $query_id = $this->query_result;


      }


      if($query_id)


      {


         $result = @mysql_field_name($query_id, $offset);


         return $result;


      }


      else


      {


         return false;


      }


   }


   function sql_fieldtype($offset, $query_id = 0)


   {


      if(!$query_id)


      {


         $query_id = $this->query_result;


      }


      if($query_id)


      {


         $result = @mysql_field_type($query_id, $offset);


         return $result;


      }


      else


      {


         return false;


      }


   }


   function sql_fetchrow($query_id = 0)


   {


      if(!$query_id)


      {


         $query_id = $this->query_result;


      }


      if($query_id)


      {


         $this->row[(int)$query_id] = @mysql_fetch_array($query_id);


         return $this->row[(int)$query_id];


      }


      else


      {


         return false;


      }


   }


   function sql_fetchrowset($query_id = 0)


   {


      if(!$query_id)


      {


         $query_id = $this->query_result;


      }


      if($query_id)


      {


         unset($this->rowset[$query_id]);


         unset($this->row[$query_id]);


         while($this->rowset[$query_id] = @mysql_fetch_array($query_id))


         {


            $result[] = $this->rowset[$query_id];


         }


         return $result;


      }


      else


      {


         return false;


      }


   }


   function sql_fetchfield($field, $rownum = -1, $query_id = 0)


   {


      if(!$query_id)


      {


         $query_id = $this->query_result;


      }


      if($query_id)


      {


         if($rownum > -1)


         {


            $result = @mysql_result($query_id, $rownum, $field);


         }


         else


         {


            if(empty($this->row[$query_id]) && empty($this->rowset[$query_id]))


            {


               if($this->sql_fetchrow())


               {


                  $result = $this->row[$query_id][$field];


               }


            }


            else


            {


               if($this->rowset[$query_id])


               {


                  $result = $this->rowset[$query_id][0][$field];


               }


               else if($this->row[$query_id])


               {


                  $result = $this->row[$query_id][$field];


               }


            }


         }


         return $result;


      }


      else


      {


         return false;


      }


   }


   function sql_rowseek($rownum, $query_id = 0){


      if(!$query_id)


      {


         $query_id = $this->query_result;


      }


      if($query_id)


      {


         $result = @mysql_data_seek($query_id, $rownum);


         return $result;


      }


      else


      {


         return false;


      }


   }


   function sql_nextid(){


      if($this->db_connect_id)


      {


         $result = @mysql_insert_id($this->db_connect_id);


         return $result;


      }


      else


      {


         return false;


      }


   }


   function sql_freeresult($query_id = 0){


      if(!$query_id)


      {


         $query_id = $this->query_result;


      }





      if ( $query_id )


      {


         unset($this->row[$query_id]);


         unset($this->rowset[$query_id]);





         @mysql_free_result($query_id);





         return true;


      }


      else


      {


         return false;


      }


   }


   function sql_error($query_id = 0)


   {


      $result['message'] = @mysql_error($this->db_connect_id);


      $result['code'] = @mysql_errno($this->db_connect_id);





      return $result;


   }





} // class sql_db





} // if ... define

registered · Posted: Tue Dec 02, 2008 6:14 am

duck, excellent work! In fact, Guardian will be quite interested in this I think as he has a new Only registered users can see links on this board! Get registered or login! that this could be an excellent addition too maybe.

Posted: Tue Dec 02, 2008 8:23 am

echo $what_montego_said . '<br />';

Posted: Wed Dec 03, 2008 6:24 am

Interesting!
My debugger will actually show you all queries executed on a given page, the amount of memory consumed by the query and also the time it took to execute, plus any errors of course.

I may include writing to a log file in the next version as that would allow for a more in depth analysis.

Posted: Wed Dec 03, 2008 6:01 pm

If we had a bunch of joins in our queries, having the explain plan would be extremely useful. It can help to identify where full table scans are being used - among other things - and give a better idea as to what types of indexes or possibly how to better re-write queries. But, my perspective is from using Oracle in a very large data warehouse situation... not MySQL I am afraid.