OpenCart SQL profiler and indexes for improved speed and page load time

indexes_to_add.sql

ALTER TABLE `oc_product_to_store` ADD INDEX(`store_id`);
ALTER TABLE `oc_product_description` ADD INDEX(`language_id`);
ALTER TABLE `oc_category_path` ADD INDEX(`path_id`, `category_id`);
ALTER TABLE `oc_category_path` ADD INDEX(`category_id`, `path_id`);

SET sql_mode = 'STRICT_TRANS_TABLES'; -- mysql 5.7
ALTER TABLE `oc_product` ADD INDEX(`status`);

-- Add these to speed up OpenCart
ALTER TABLE `oc_product` ADD INDEX(`date_available`, `status`);
ALTER TABLE `oc_url_alias` ADD UNIQUE(`query`);
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`);

oqmod_opencart_profiler_v_1.1.ocmod.xml

<?xml version="1.0" encoding="utf-8"?>
<modification>
	<code>sql_profiler_kts</code>
    <link>http://ktsstudio.ru</link>
	<name>Opencart Profiler 1.0 for 2.2.0.0</name>
	<id>kts-sql-profiler</id>
	<version>1.0.0</version>
	<author>grigory51 (fork OCShop)</author>
	

	<file path="system/library/response.php">
		<operation>
			<search><![CDATA[class Response {]]></search>
			<add position="after"><![CDATA[
				public function __construct() {
					$GLOBALS['start'] = microtime();
    			}
			]]></add>
		</operation>
		<operation>
			<search><![CDATA[echo $output;]]></search>
			<add position="after"><![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="max-width: 100%; margin: 0 auto; overflow: 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 path="system/engine/controller.php">
		<operation>
			<search><![CDATA[	$this->registry = $registry;]]></search>
			<add position="before"><![CDATA[
			$GLOBALS['controller_name'] = get_class ($this);
			]]></add>
		</operation>
	</file>

	<file path="system/library/db.php">
		<operation>
			<search><![CDATA[public function query($sql, $params = array()) {]]></search>
			<add position="after"><![CDATA[$starttime = microtime(true);
			]]></add>
		</operation>

		<operation>
			<search><![CDATA[return $this->adaptor->query($sql, $params);]]></search>
			<add position="replace"><![CDATA[
			$result = $this->adaptor->query($sql, $params);
			$finishtime = microtime(true) - $starttime;
			if (!isset($GLOBALS['controller_name'])) $GLOBALS['controller_name'] = '';
			$GLOBALS['sql'][] = $sql. '[sep]'. $finishtime . '[sep]'.	 ($GLOBALS['controller_name']) ;
			return $result;
			]]></add>
		</operation>
	</file>
</modification>