osC-Explain v1.0 for osCommerce-MS2

by Chemo

What does this contribution do?

This contribution adds the ability to store the EXPLAIN data return for each query that is executed on an osC store. In addition, the included admin report provides excellent functionality to view the data in a coherent format.  This is the tool that I use to optimize client sites and have decided to release it to the osC contribution coder community.  We're all coders...this one I'm going to make an open call to all of you to make improvements and upload back to the contribution area.  This one is by the coders and for the coders.  I encourage each of you to make this a more feature rich contribution so we can all benefit.

Features:

• Auto-install database settings script: no need to "execute with phpMyAdmin".  Just upload and point your browser to the install script where you can install AND UNINSTALL the database settings.
• Global on/off setting for storing EXPLAIN => in the control panel
• Select specific scripts to explain.  The rest will be ignored.  This is handy if you want to track only specific pages that you are coding or have modified for a contribution => in the control panel
• Select specific pages to exclude from explaining.  Handy if you have optimized a few pages for a contribution but want to see how the rest are performing => in the control panel
• Drop down selection menu filter for scripts with instant view of (unique queries / total queries) for that script => on the report
• Set the record return limit and offset with quick input fields allow you to jump anywhere in the results quickly => on the report
• Export data as CVS or HTML => on the report
• Data output has clickable link for query to help quickly drill down through the data for problem queries.  Instantly find out which scripts have executed that query, min-average-max execution times, and much more data => on the report
• One click truncate table button to easily reset the table and start over.  Handy to keep all tools in one spot => on the report
• One click analyze table button to easily reset the index of the table and make the report run faster on large data sets => on the report

How does this contribution work?

This contribution first verifies that EXPLAIN_QUERIES is true and then uses simple criteria to determine whether to store the query.  Once a query is flagged to be EXPLAINED it will stored in the `explain_queries` table.  Queries can be EXPLAINED and stored for single pages, groups of pages, or site wide.  Very flexible in storage criteria.

Once enough data is collected (I recommend at least a 1:10 ratio of unique:total) use the report to view and export the data.

Screenshots

Main Explain Queries report - click to see full sized image

CVS data export - "Save As" dialog box (on my Linux box, Mozilla)

HTML data export - click to see full sized image

How do I install this contribution?

STEP 1 - Upload the included files

The files should be located in the upload directory of this contribution.

• admin/stats_explain_queries.php should be uploaded to your *catalog*/admin/ directory
• admin/install-explain.php should be uploaded to your *catalog*/admin/ directory.

STEP 2 - Call *admin*/install-explain.php in your browser

This script has two options: install or uninstall. I think you can figure out what each does. Read the page.

STEP 3 - Edit *catalog*/includes/functions/database.php

Find the function tep_db_query()

function tep_db_query($query, $link = 'db_link') { global $$link; if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true')) { error_log('QUERY ' . $query . "\n", 3, STORE_PAGE_PARSE_TIME_LOG); } $result = mysql_query($query, $$link) or tep_db_error($query, mysql_errno(), mysql_error()); if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true')) { $result_error = mysql_error(); error_log('RESULT ' . $result . ' ' . $result_error . "\n", 3, STORE_PAGE_PARSE_TIME_LOG); } return $result; }

REPLACE WITH THIS CODE:

function tep_db_query($query, $link = 'db_link') { global $$link; if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true')) { error_log('QUERY ' . $query . "\n", 3, STORE_PAGE_PARSE_TIME_LOG); } $_start = explode(' ', microtime()); $result = mysql_query($query, $$link) or tep_db_error($query, mysql_errno(), mysql_error()); $_end = explode(' ', microtime()); $_time = number_format(($_end[1] + $_end[0] - ($_start[1] + $_start[0])), 8); if ( defined('EXPLAIN_QUERIES') && (EXPLAIN_QUERIES == 'true') ){ /* Initially set to store every query */ $explain_this_query = true; /* If the include filter is true just explain queries for those scripts */ if ( defined('EXPLAIN_USE_INCLUDE') && (EXPLAIN_USE_INCLUDE == 'true') ){ $explain_this_query = ( ( stripos( EXPLAIN_INCLUDE_FILES, basename($_SERVER['PHP_SELF']) ) ) === false ? false : true ); } /* If the exclude filter is true just explain queries for those that are not listed */ if ( defined('EXPLAIN_USE_EXCLUDE') && (EXPLAIN_USE_EXCLUDE == 'true') ){ $explain_this_query = ( ( stripos( EXPLAIN_EXCLUDE_FILES, basename($_SERVER['PHP_SELF']) ) ) === false ? true : false ); } /* If it still true after running it through the filters store it */ if ($explain_this_query) tep_explain_query($query, $_time); } # End if EXPLAIN_QUERIES if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true')) { $result_error = mysql_error(); error_log('RESULT ' . $result . ' ' . $result_error . "\n", 3, STORE_PAGE_PARSE_TIME_LOG); } return $result; } function wrap(&$array, $wrapwith="'"){ foreach ($array as $index => $value){ if ($value!='') $array[$index] = $wrapwith . tep_db_input($value) . $wrapwith; else $array[$index] = "''" ; } } function tep_explain_query($query, $_time, $link = 'db_link') { global $$link; /* Makes sure it's a select query and it's not for a session */ if ( stristr($query, 'select') && !stristr($query, 'sessions') ) { /* Add the EXPLAIN to the query */ $explain_query = 'EXPLAIN ' . $query; $_query = array('explain_id' => '', # Leave blank to get an autoincrement 'md5query' => md5($query), # MD5() the query to get a unique that can be indexed 'query' => $query, # Actual query 'time' => $_time*1000, # Multiply by 1000 to get milliseconds 'script' => basename($_SERVER['PHP_SELF']), # Script name 'request_string' => $_SERVER['QUERY_STRING'] # Query string since some pages are constructed from parameters ); /* Merge the _query and explain arrays */ $container = array_merge($_query, mysql_fetch_assoc(mysql_query($explain_query))); /* Break the array into components so elements can be wrapped */ foreach($container as $column => $value){ $columns[] = $column; $values[] = $value; } /* Wrap the columns and values */ wrap($columns, '`'); wrap($values); /* Implode the columns so they can be used for the insert query below */ $_columns = implode(', ', $columns); $_values = implode(', ', $values); /* Insert the data */ $explain_insert = "INSERT into `explain_queries` ($_columns) VALUES ($_values)"; mysql_query($explain_insert) or tep_db_error($explain_insert, mysql_errno(), mysql_error()); /* unset some variables...clean as we go */ unset( $_query, $container, $columns, $values, $_columns, $_values ); } }

STEP 4 - Edit *admin*/includes/boxes/reports.php

Add a link to the stats_explain_queries.php file. I'm not giving directions since I expect you to already know how to do this. If you don't know how please uninstall this contribution.

STEP 5 - Admin Control Panel -> Configuration -> Explain Queries

Set your options and let the table populate with data!

Notes

The installation is pretty straight forward. The target audience for this contribution are coders, developers, and advanced webmasters that know what the hell they are doing.  Thus, if you are clueless and can't figure it out and/or correct the install problem please email: blackhole@mesoimpact.com

Support

osCommerce is a community driven organization and as such the support base will fall entirely on the [coder and developer] forum members. The code is thoroughly commented and should be easy to follow for any coder. I offer limited support in-between paid projects (feeding my family comes first before volunteer time).

If you use this contribution and find it useful a small donation can be made via PayPal.  If you're a coder, designer, or webmaster and would like to contract me for site analysis or custom coding please email me with the link at the top of this page.  Thank you for your support!

Enter Donation amount: $

Credits

This contribution is original work. Enjoy!