Database optimization and caching of sql queries in OpenCart 2.0-2.2
Optimization of the online store database is a very difficult issue, sometimes requiring separate research.
And the most unpleasant thing in this process is that only someone who knows sql queries and understands databases (DB) can do at least some optimization.
That's just to ensure that customers do not have to deal with what they do not need to know, and created IMDBOptimizer. Capabilities:
1. A hybrid system for caching SQL queries (DB + files), which allows increasing the speed of generating HTML pages (tested on standard OpenCart with 5500 products - performance increase from 30% to 70-80%) and partially balancing the load between the disk and the database.
2. The filter "by words" is supported to exclude SQL queries from the caching process (case insensitive).
3. The filter "by URL" is supported to exclude individual pages from the process of caching SQL queries (case insensitive).
4. Since only SQL queries are cached, such a module can be successfully used together with other caching modules (for example, v2pagecache). However, compatibility is best checked on a test server.
5. Have you installed the module? Nothing needs to be configured 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 of 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, they opened the same product from different categories - the options will be cached only 1 time.
7. It can be applied both with the creation of indexes and without.
8. During installation, the module immediately creates a typical setup, 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 caching module of the database level, it must be borne in mind that such features as displaying the actual balance of the goods or the current price in the card are not supported (the data is cached).
2. Only SQL queries starting with select are cached.
3. The registry.php sound file is replaced
4. Caching is applied only to the client part, in the admin part all requests are executed as usual.
5. Consider that caching is an additional load. For example, when you first open the product page, it may take longer to load (a cache is created).