Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
OpenCart SQL profiler and indexes for improved speed and page load time. Requires vqMod to be installed. Place it in <project-root>/vqmod/xml/ and that's it. Copy and paste the indexes once in PhpMyAdmin, in the database for your store.
-- Add these to speed up OpenCart
ALTER TABLE `oc_product` ADD INDEX(`date_available`, `status`);
ALTER TABLE `oc_product` ADD INDEX(`status`);
ALTER TABLE `oc_url_alias` ADD UNIQUE(`query`);
ALTER TABLE `oc_product_to_store` ADD INDEX(`store_id`);
ALTER TABLE `oc_category_path` ADD INDEX(`path_id`, `category_id`);
ALTER TABLE `oc_category_path` ADD INDEX(`category_id`, `path_id`);
ALTER TABLE `oc_product_description` ADD INDEX(`language_id`);
ALTER TABLE `oc_category` ADD INDEX (`parent_id`, `status`, `sort_order`);
ALTER TABLE `oc_category` ADD INDEX(`sort_order`);
ALTER TABLE `oc_product_to_category` ADD INDEX (`category_id`);
ALTER TABLE `oc_category_description` ADD INDEX (`language_id`);
ALTER TABLE `oc_category_to_store` ADD INDEX (`store_id`);
ALTER TABLE `oc_product_attribute` ADD INDEX (`attribute_id`), ADD INDEX (`language_id`);
ALTER TABLE `oc_product` ADD INDEX (`manufacturer_id`);
ALTER TABLE `oc_setting` ADD INDEX(`store_id`);
ALTER TABLE `oc_review` ADD INDEX(`status`, `product_id`);
ALTER TABLE `oc_extension` ADD INDEX(`type`);
ALTER TABLE `oc_tag_cloud` ADD INDEX( `language_id`, `store_id`);
<modification>
<id>Opencart Profiler 1.1 for 1.5.1 - 1.5.5.x</id>
<version>1.5.x</version>
<vqmver required="true">1.0.0</vqmver>
<author>OCShop|| Please visit modules.ocshop.biz</author>
<file name="system/startup.php">
<operation error="log">
<search position="after"><![CDATA[error_reporting(E_ALL);]]></search>
<add><![CDATA[
$time = microtime();
$GLOBALS['start'] = $time;
]]></add>
</operation>
</file>
<file name="system/library/response.php">
<operation error="log">
<search position="after"><![CDATA[echo $ouput;]]></search>
<add><![CDATA[
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$start = explode(' ', $GLOBALS['start']);
$start = $start[1] + $start[0];
$total_time = round(($finish - $start), 4);
$queries = $GLOBALS['sql'];
echo '<div id="debug" style="position:relative; bottom:0; z-index:1000; width:100%;min-height:100px; padding:20px; background: darkred; "><div style="width:1000px;margin:0 auto;">';
echo '<div style="color:white; font-size:14px; line-height:20px">Total time ' . $total_time. ' seconds | ';
echo 'Total queries:' . count($GLOBALS['sql']) . '</div>';
$query_times = array();
$query_counts = array();
foreach ($queries as $query_profile) {
list($query, $time, $controller) = explode ('[sep]', $query_profile);
// Remove single quotes
$query = str_replace("\\'", '', $query);
// Remove values from inside single quotes
$query = preg_replace('/\'[^\']*\'/', "'<span style='background: #ccc;'>...</span>'", $query);
// Remove unquoted IDs
$query = preg_replace('/\b\d+\b/', "<span style='background: #ccc;'>&lt;number&gt;</span>", $query);
$querytime = round($time, 5);
if (!isset($query_times[$query])) {
$query_times[$query] = 0.0;
$query_counts[$query] = 0;
}
$query_times[$query] += $querytime;
$query_counts[$query]++;
}
// Sort the profile summary putting the slowest columns at the top
arsort($query_times);
echo '<style>
.profile-summary {
background: white;
border-collapse: collapse;
}
.profile-summary th,
.profile-summary td {
border: 1px solid #ccc;
padding: 2px 8px;
vertical-align: top;
}
.profile-summary td.time {
text-align: right;
}
</style>';
echo '<table class="profile-summary">';
echo '<tr>
<th>Time</th>
<th>Count</th>
<th>Query</th>
</tr>';
foreach ($query_times as $query_pattern => $time) {
echo '<tr>
<td class="time">' . number_format($time, 5) . 's</td>
<td>' . $query_counts[$query_pattern] . '</td>
<td>' . $query_pattern . '</td>
</tr>';
};
echo '</table>';
echo '</div></div>';
]]></add>
</operation>
</file>
<file name="system/engine/controller.php">
<operation error="log">
<search position="before"><![CDATA[ $this->registry = $registry;]]></search>
<add><![CDATA[
$GLOBALS['controller_name'] = get_class ($this);
]]></add>
</operation>
</file>
<file name="system/library/db.php">
<operation error="log">
<search position="after"><![CDATA[public function query($sql) {]]></search>
<add><![CDATA[$starttime = microtime(true);
]]></add>
</operation>
<operation error="log">
<search position="replace"><![CDATA[return $this->driver->query($sql);]]></search>
<add><![CDATA[
$result = $this->driver->query($sql);
$finishtime = microtime(true) - $starttime;
if (!isset($GLOBALS['controller_name'])) $GLOBALS['controller_name'] = '';
$GLOBALS['sql'][] = $sql. '[sep]'. $finishtime . '[sep]'. ($GLOBALS['controller_name']) ;
//print_r ($GLOBALS['controller_name']);
return $result;
]]></add>
</operation>
</file>
</modification>
@supak111

This comment has been minimized.

Copy link

commented Mar 13, 2015

Does this work with any OC including 2.0.1.1? Total newbe in DB sorry. My site has a very slow first byte load and I was told indexing DB will help a lot.

@alpapazzo

This comment has been minimized.

Copy link

commented May 17, 2017

Thank you it is great!

@tunnabulutlu

This comment has been minimized.

Copy link

commented Nov 26, 2017

hi,

please oc 2.10.2 please :(

@oberheimer

This comment has been minimized.

Copy link

commented Aug 1, 2018

Hello do you have this for opencart 3?
I could really need this.

@bezdelnique

This comment has been minimized.

Copy link

commented Aug 6, 2018

+1 Need it for oc3.
I've found free module and install it. But don't understand how to do it active.

https://www.opencart.com/index.php?route=marketplace/extension/info&member_token=&extension_id=15197&filter_download_id=55&sort=price

@florinsith

This comment has been minimized.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.