Reduced database access by using booleans for reference tables
For VirtueMart 3.6.11 and higher
To perform fast database queries VirtueMart (for some data types) stores data as arrays in additional reference tables.
An example of this is the storing of categories to which a product has been allocated. If this data was stored in the product table (as a simple list of comma separated references), VirtueMart could not use fast SQL queries to list products of a certain category. Instead it would have to load products and sort later, thus removing one of the advantages of our SQL database.
Reduce unnecessary refrence table loads
During our code review we noted that (very often) VirtueMart was loading reference tables unnecessarily. For example, when using calculation rules, these often have 1-2 restrictions (country and category) but they offer 5 restrictions but still 5 tables were loaded. Clearly it would be better, and faster, if we only loaded reference tables when there is relevant data in them.
New has_x fields in existing tables
To improve things VirtueMart has now added a series of indicator fields "has_x" (boolean) to the main tables, these indicate when an object has referencing data in another table. If there is no referencing data, the table is no longer loaded this functionality reduces sql requests for most cases. This method is not without some effort, as from now on, updating an object also has to update the boolean fields.
In the main tables we store additional "has_x" fields to indicate if other tables may need to be involved in processing.
The core creates the new "has_x" fields in the database with the default value "Null". If the core loads an item where "has_x" is "Null" it executes an update of the "has_x" values in the relevant table, this works when viewing lists of items in FE and BE.
Third party synchronisers and table updaters
Such new functionality is important for old synchronisers. As they are unlikely to fill the has_x fields and leave them as is. So old synchronisers need to be updated, or modified to execute the updating function themselves.
We have added a new button in the tools section, which can be executed manually. It is also helps as a hint for developers where to search for the update functions. It is better, of course, to write the fields directly with the correct values, but the update function works reasonably quickly for bigger shops (usually within 30 seconds max execution time).
$model = $this->getModel('updatesMigration');
$model->reset_Has_x_Fields();
Configuration options
These new optimisations are set as on by default when upgrading/installing - they can be disabled in the VirtueMart configuration. NOT RECOMMENDED.
It should be quite easy to find a solution to a problem if you encounter one. Most 3rd party problems should be fixable with some lines calling the table xref boolean updater function at the end of their job.
Disabling the optimisations for the categories will use the old category_categories table again, as the data is still stored in the category_categories table, switching should not present a problem.
Category_categories table
Working on this new functionality it was further identified that the "category_categories" table is no longer needed.
This table was added long time ago (maybe even before VM1 possibly planned as nested set, or to use categories more than once in the category tree). We never had the request to use a category more than once within the category tree, so we just reject this idea. We noticed that the nested set is, for most shops, slower than the adjacency list. The reason is that the nested set is slower for trees with a depth of only 2. It is faster for loading trees with more than 2 levels, but this scenario is less common amongst our user base. Additionally the nested set is great for example for permission trees to load directly all permissions of a certain level. However, in most shops we usually want to load just one level of categories. We don’t want to show directly the lower subcats as well (except in the category tree module.) For fall-back support the category_categories table is still available and updated when storing a category.