Optimizing an online store database is a very difficult issue, sometimes requiring separate research.
And the most unpleasant thing about this process is that only someone who knows SQL queries and understands databases can do at least some optimization.
It is precisely to ensure that clients do not have to deal with what they do not need to know that IMDBOptimizer (OC 3) was created.
I strongly recommend that you read the review https://liveopencart.ru/tips/sovety-po-optimizatsii-opencart-o-kotoryh-stoit-znat-kazhdomu/
Articles about modules or just useful materials:
1. What are database indexes (for beginners)?
2. Indexes and a bit of tricky mathematics
3. Testing IMDBOptimizer for 2000 and 5500 products (indices)
4. An example of the benefits of indexes and IMDBOptimizer
5. Testing IMDBOptimizer for 5500 products - SQL query cache
Module demo
Administrator demo (demo/demo)
1-Click Optimization
The module allows you to perform optimization in 1 click: create indexes, optimize tables (sql query optimize table), and also enable SQL cache, if this is required.
Peculiarity. If you select in the "Enable cache?" value “Disabled”, the module will not disable the cache if it was enabled. This is specifically done so that you don't accidentally disable the cache when you periodically check whether you need to create indexes and optimize tables.
A small nuance for rare cases. Keep in mind that, for example, if indexes take too long to create (more details in “What to do if indexes take too long to create?”), then you will have to create them through the tab, and not through 1-click optimization. This is a purely technical limitation.
Caching SQL Queries
OpenCart, like any CMS, carries out a considerable number of SQL queries to the database, some of which are of the same type (that is, for different users there will be the same result).
And if there are a lot of products, then SQL queries can easily become the main reason for the slowdown of the online store (if you have 5000+ products, then you are probably well aware of this).
However, this can be avoided by caching sql queries with the IMDBOptimizer module.
Possibilities:
1. A hybrid SQL query caching system (DB + files), which allows you to increase the speed of HTML page generation (tested on standard OpenCart with 5500 products - performance increase from 30% to 70-80%) and partially balance the load between the disk and the DB.
2. A “by words” filter is supported to exclude SQL queries from the caching process (case-insensitive).
3. A URL filter is supported to exclude individual pages from the process of caching SQL queries (case-insensitive).
4. Since only SQL queries are cached, this module can be successfully used in conjunction with other caching modules (for example, v2pagecache). However, it is better to check compatibility on a test server.
5. Have you installed the module? You don't need to configure anything for caching. SQL queries automatically begin to be cached (subject to filters), without the need to configure anything else.
6. Another distinctive feature of caching SQL queries is that if the same query is used to generate different web pages or is simply executed repeatedly, then only one cache is used. A simple example: if you open the same product from different categories, the options will be cached only once.
7. Can be used both with the creation of indexes and without.
8. When installed, the module immediately creates a standard setting; you just need to enable the cache.
9. Easy to turn on and easy to turn off.
Limitations and nuances:
1. Since this is a database-level caching module, it is necessary to take into account that such features as displaying the real balance of an item or the current price in a card are not supported (the data is cached).
2. Only SQL queries starting with select are cached.
3. The core file registry.php is replaced
4. Caching is applied only to the client part; in the admin part, all requests are executed as usual.
5. Keep in mind that caching is an additional burden. For example, when you first open a product page, it may take longer to load (a cache is created).
And the most unpleasant thing about this process is that only someone who knows SQL queries and understands databases can do at least some optimization.
It is precisely to ensure that clients do not have to deal with what they do not need to know that IMDBOptimizer (OC 3) was created.
I strongly recommend that you read the review https://liveopencart.ru/tips/sovety-po-optimizatsii-opencart-o-kotoryh-stoit-znat-kazhdomu/
Articles about modules or just useful materials:
1. What are database indexes (for beginners)?
2. Indexes and a bit of tricky mathematics
3. Testing IMDBOptimizer for 2000 and 5500 products (indices)
4. An example of the benefits of indexes and IMDBOptimizer
5. Testing IMDBOptimizer for 5500 products - SQL query cache
Module demo
Administrator demo (demo/demo)
1-Click Optimization
The module allows you to perform optimization in 1 click: create indexes, optimize tables (sql query optimize table), and also enable SQL cache, if this is required.
Peculiarity. If you select in the "Enable cache?" value “Disabled”, the module will not disable the cache if it was enabled. This is specifically done so that you don't accidentally disable the cache when you periodically check whether you need to create indexes and optimize tables.
A small nuance for rare cases. Keep in mind that, for example, if indexes take too long to create (more details in “What to do if indexes take too long to create?”), then you will have to create them through the tab, and not through 1-click optimization. This is a purely technical limitation.
Caching SQL Queries
OpenCart, like any CMS, carries out a considerable number of SQL queries to the database, some of which are of the same type (that is, for different users there will be the same result).
And if there are a lot of products, then SQL queries can easily become the main reason for the slowdown of the online store (if you have 5000+ products, then you are probably well aware of this).
However, this can be avoided by caching sql queries with the IMDBOptimizer module.
Possibilities:
1. A hybrid SQL query caching system (DB + files), which allows you to increase the speed of HTML page generation (tested on standard OpenCart with 5500 products - performance increase from 30% to 70-80%) and partially balance the load between the disk and the DB.
2. A “by words” filter is supported to exclude SQL queries from the caching process (case-insensitive).
3. A URL filter is supported to exclude individual pages from the process of caching SQL queries (case-insensitive).
4. Since only SQL queries are cached, this module can be successfully used in conjunction with other caching modules (for example, v2pagecache). However, it is better to check compatibility on a test server.
5. Have you installed the module? You don't need to configure anything for caching. SQL queries automatically begin to be cached (subject to filters), without the need to configure anything else.
6. Another distinctive feature of caching SQL queries is that if the same query is used to generate different web pages or is simply executed repeatedly, then only one cache is used. A simple example: if you open the same product from different categories, the options will be cached only once.
7. Can be used both with the creation of indexes and without.
8. When installed, the module immediately creates a standard setting; you just need to enable the cache.
9. Easy to turn on and easy to turn off.
Limitations and nuances:
1. Since this is a database-level caching module, it is necessary to take into account that such features as displaying the real balance of an item or the current price in a card are not supported (the data is cached).
2. Only SQL queries starting with select are cached.
3. The core file registry.php is replaced
4. Caching is applied only to the client part; in the admin part, all requests are executed as usual.
5. Keep in mind that caching is an additional burden. For example, when you first open a product page, it may take longer to load (a cache is created).