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).
SQL Query Caching - Enable and Clear
How to enable caching:
1. Go to the “SQL Query Cache” tab.
2. Set caching to Enabled and save the settings.
3. Cache enabled
To disable you need to do the same thing, only in step 2 set “Disabled”
How to clear cache:
1. Go to the “SQL Query Cache” tab
2. Click the "Delete cache" button
3. Wait for the corresponding message
SQL Query Caching - Filters
Filter queries “by words”:
In this filter, phrases that should not be in the request are indicated line by line (converted to lower case). By default, a list of tables/prefixes is specified for the standard configuration.
When filtering, the “#” symbol will be replaced with the database prefix, which allows you to create your own configurations that can be easily transferred to other online stores.
Important! Spaces are also taken into account - this is done so that individual tables can be excluded. For example, the string "#order" without a space after order excludes all tables associated with the order. And with a space after it, only oc_order is excluded (it is also recommended to duplicate the rule and specify an apostrophe after order, since SQL queries are formed differently).
Empty lines are ignored. Spaces at the beginning and end of the SQL query are also ignored.
Request filter “by URL”:
In this filter, the combinations “[search type]: [part of the URL]” (or simply “[part of the URL]”) are specified line by line to disable caching when generating specific pages (such as the cart or checkout). All combinations are converted to lowercase (case insensitive).
1. [part of the URL] is some part of the URL, for example, “#/cart/” or “=checkout/”.
2. [search type] – has three values: “l” (compare part of the address first with the URL; query parameters are taken into account), “i” (search for part of the address inside the URL; query parameters are taken into account), “r” (search for part of the address on the right; request parameters are not taken into account).
When filtering, the “#” symbol will be replaced with the site domain, which allows you to create your own configurations that can be easily transferred to other online stores.
By default, a list of filters is provided for standard settings, as well as a basket with the Simple module.
Also keep in mind that the “http://” and “https://” prefixes are trimmed, and that if the “[search type]:” fragment is not specified, then the search occurs on the left (so that you can simply paste the URL).
Examples:
r: #/cart
“site.ru/cart”, “site.ru/cart?asd=1” are suitable. But “site,ru/cartini” does not fit
i: =checkout/
“site.ru/index.php?route=checkout/checkout&1=2” is suitable. But “site.ru/index.php?route=check/some” doesn’t work.
Caching SQL queries - additional settings
The maximum size of an insert into the database. This parameter specifies beyond what size of data the information must be cached in a file. The maximum value is 65000. It is recommended to use in the range of 20000 - 30000.
Cache lifetime (sec). Here you can specify the time in seconds during which the created cache will be active.
Minimum cache time (ms). Here you set the time in ms, which determines the minimum execution time of an sql query, above which the query is cached. For example, if it costs 20 ms, and the request was completed in 10 ms, then it is not cached. If the request was completed in 21 ms, then it is cached.
sql caching tables: MyISAM and InnoDB type
It is possible to choose what type of table will be used to store the sql cache in the database: MyISAM or InnoDB type.
Please note that physically these are two different tables in the database and that the cache delete button clears both tables.
If you don’t know what it is and why it’s needed, then use the MyISAM type (used by default in the module).
Only one of the two tables can be used at a time. Please note that switching tables must be performed with the SQL cache turned off and cleared, as errors may potentially occur. Whatever table is used, the file cache remains the same.
For those who only need basic optimization
If you do not plan to add additional indexes yourself (or leave it to those who understand this), then, as I already said, the module contains default settings for basic database optimization.
Important! Please note that the operation is quite lengthy and that minimal load on the database is required (that is, it is desirable that there are no or few site users). As a last resort, you can sequentially and separately create indexes for each of the tables, rather than all at once.
1. Make a backup of the entire database (or better yet, the site as a whole)! It is important!
2. Open the module
3. Select all tables (you can click on the “Select all” link)
4. Just below, click on the “Generate!” button.
5. Sit back and watch the module create indexes
A few clicks and you have basic database optimization done!
