In a previous article we explained how to speed up your OpenCart store by reducing the calculation for the number of products displayed in a given category.
In this way you reduce the numerous SQL queries, sent towards the database. If you need the number of products to be visible, you can optimize in another way in order to improve your loading speed.
Today we will tell you about one more basic technique for optimizing and speeding up your OpenCart.
This optimization involves the store database and can be executed independently from the one described in the previous article. If you combine the two ways, however, you will notice much bigger improvement in the loading speed.
When a page is loaded not only an SQL query for calculating the number of products in each category is executed, but also many more.
The OpenCart option for creating SEO friendly URLs is a source of SQL queries. Each URL and its corresponding SEO friendly URL are saved in the database. When a store page is loaded at least a few SQL queries are sent to the database for each category, product or anything else. We have seen cases where only the store home page with 30 products is loaded, but this resulted in the execution of 847 SQL queries, as 510 are SEO friendly URLs, 185 are generated by the template and only 179 for products, options, settings, etc.
If the number of SQL queries cannot be reduced, you can optimize the search for information in the database. By placing indexes in the most searched tables, SQL queries will be able to find and retrieve quickly that information.
The most popular example of a database index
The entries (lines) in a phonebook are ordered in alphabetical order according to the names’ first letter. To look for Ivan in a phonebook you should open the page with names, starting with the letter “I”. This ordering is something like a main index. In other words, the names are ordered in accordance to their initial letter. This saves you a lot of time and efforts instead of going through all of the phonebook pages. If you decide to look by city, the lines with cities are scattered. You will need to go through every page of the phonebook in order to find a person from that city.
But if the city sections are indexed, you will know the exact lines which contain this city name. This second index is like a virtual rearranging of information but by another parameter instead of names. When you are looking by city you can use an alphabetically ordered list with the city names, thus finding the right information will be much faster.
Indexes facilitate information finding in the database tables. When the information is searched in a given table column, this column can have an index created.
The optimization, described in the following lines is exactly about creating indexes in certain database tables. If you use a hosting account with cPanel you only need to access the database administration tool – phpMyAdmin.
Backup Your Database
Before making any changes, make sure that you have a ready database backup so that you are able to quickly restore it to its previous state.
Manual Backup and Recovery
You can backup manually through cPanel -> Backups ->(Full backup). Restoring the database from this backup can also be done manually.
If the database is of smaller size, you can quickly create a copy through phpMyAdmin. Select the database, click on Оperations and fill in the new database name under the Copy database to section (i.e. cpaneluser_newdb).
Automatic Backup and Recovery
Database backup is done automatically and it is present in the system generated backup of the hosting account. We recommend using Backup Manager by SuperHosting in the cPanel in order to restore the database from the system backup. You can see the date and time of the last backup generated in the Manager. If you have not made any changes besides optimization after the last backup, you can easily restore the previous database without losing any information.
Optimizing Database
Important: Placing indexes in certain tables can help speed up the frontend loading of the store, but it can also cause slow admin panel loading. You will notice slowdown when a new category or a product are added, because the existing index in the respective table is recreated (updated).
As you change every table step by step you can check for the speed and proper loading of your store.
The coded lines described are actually SQL queries which you can execute as you copy and place them in the SQL section of phpMyAdimin. The index is permanent and you do not need to place it again in the future. Check carefully and, if necessary, change the table prefix (in the examples given the prefix is oc_).
Category Tables
ALTER TABLE `oc_category` ADD INDEX ( `parent_id` ) ; ALTER TABLE `oc_category` ADD INDEX ( `top` ) ; ALTER TABLE `oc_category` ADD INDEX ( `sort_order` ) ; ALTER TABLE `oc_category` ADD INDEX ( `status` ) ; ALTER TABLE `oc_category_description` ADD INDEX ( `language_id` ); ALTER TABLE `oc_category_to_store` ADD INDEX ( `store_id` );
If you have many subcategories with their own subcategories, place an index to the path_id column:
ALTER TABLE `oc_category_path` ADD INDEX ( `path_id` );
Product Tables
ALTER TABLE `oc_product` ADD INDEX ( `model` ) ; ALTER TABLE `oc_product` ADD INDEX ( `sku` ) ; ALTER TABLE `oc_product` ADD INDEX ( `upc` ) ; ALTER TABLE `oc_product` ADD INDEX ( `manufacturer_id` ) ; ALTER TABLE `oc_product` ADD INDEX ( `sort_order` ) ; ALTER TABLE `oc_product` ADD INDEX ( `status` ) ; ALTER TABLE `oc_product_option` ADD INDEX ( `option_id` ) ; ALTER TABLE `oc_product_option_value` ADD INDEX ( `product_option_id` ) ; ALTER TABLE `oc_product_option_value` ADD INDEX ( `product_id` ) ; ALTER TABLE `oc_product_option_value` ADD INDEX ( `option_id` ) ; ALTER TABLE `oc_product_option_value` ADD INDEX ( `option_value_id` ) ; ALTER TABLE `oc_product_to_category` ADD INDEX ( `category_id` ); ALTER TABLE `oc_product_attribute` ADD INDEX ( `attribute_id` ); ALTER TABLE `oc_product_attribute` ADD INDEX ( `language_id` ); ALTER TABLE `oc_product_description` ADD INDEX ( `language_id` ); ALTER TABLE `oc_product_to_store` ADD INDEX ( `store_id` );
Tables for Product Features
If you have multiple product features, add indexes in the following tables:
ALTER TABLE `oc_option` ADD INDEX ( `sort_order` ) ; ALTER TABLE `oc_option_description` ADD INDEX ( `name` ) ; ALTER TABLE `oc_option_value` ADD INDEX ( `option_id` ) ; ALTER TABLE `oc_option_value_description` ADD INDEX ( `option_id` ) ;
Seo Friendly URLs Table
ALTER TABLE `oc_url_alias` ADD INDEX ( `query` ) ; ALTER TABLE `oc_url_alias` ADD INDEX ( `keyword` ) ; ALTER TABLE `oc_url_alias` ADD INDEX ( `url_alias_id` );
After creating such indexes, we have noticed that in some cases loading time is considerably reduced. When combined with optimization by stopping the calculated and displayed number of products in categories, we noticed even bigger acceleration.
Tip from the support: The overall optimization includes other changes which you can make, for example to enable mod_deflate and Memcached, use mod_expires, etc. Optimizing techniques also include usage of SSDs for MySQL database.
Tip from the support: Тhis optimization instruction resulted from frequent requests from our customers. But it does not cover all possible solutions for each OpenCart store. If our tips are not enough, we recommend you to look for help from experts in this field. They can conduct a detailed examination of your store and find the right way to optimize it.
