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.

indexes_to_add.sql

-- 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`);

vqmod_opencart_sql_profiler.xml

<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>

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.

Thank you it is great!

hi,

please oc 2.10.2 please :(

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

+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

Oc 2 version here: https://gist.github.com/florinsith/265255891dcf939ca524a9837dc8cca3