Thoughts on Virtuemart 2.0, Cherry Picker, Product Types and Custom Fields
January 24, 2012
With new versions of Joomla and Virtuemart having been released recently there had been quite a few inquiries about the future of Cherry Picker and filtering itself.
That is why I want to share some thoughts on this matter.
As for Joomla, either it is 1.6 or 1.7 or any other future release—it doesn't matter: Joomla is not an obstacle in any way. It's simply a question of changing installer .xml file which is not a problem.
The main question rests with Virtuemart 2.0.
In the new version VM developers completely abandoned good old Product Types in favor of new Custom Fields.
I would like to illustrate that Custom Fields do not seemingly bring any advantages, but on the other hand have quite a few of drawbacks.
Let's have a comparative example between Product Type and Custom Fields (we will not delve deep into details).
Let us say, we have 2,000 products and 2 Product Types, each has 1,000 products. And each Product Type has 5 Parameters (like: Brand, Screen, Ram, Hdd, OS).
In Virtuemart 1.1 this would be represented in Database with 2 tables: "vm_product_type_1" and "vm_product_type_2" with 6 columns in each: first column is "product_id" and rest five for each parameter.
Naturally, each of two tables would have 1,000 records.
And when performing a search MySQL will look up only among 1,000 records.
Now let's see how Custom Fields would store 2,000 products' filters in Database.
All values are stored in one table "virtuemart_product_customfields". Every Parameter is being inserted as new record, so one product with our 5 parameters will have 6 records: 5 records for parameters plus 1 record for parent-custom-field (the one that stores 5 custom fields as children).
So our 2,000 products will end up as 12,000 records.
Now, from a logical point of view, in which case 50* SQL queries will perform better (look up from 1K or 12K of records)? :)
* - It's just an example for 50 filters that make 50 queries.
As well as you can in Fast Seller:
With Custom Fields you can choose only one value.
Well you can skip to define Possible Filters and then enter values by hand for each product and then add a semicolon, like: Red;White;Blue
But that is not really efficiently plus we would have to make another operation of collecting all used filters with more SQL query (look up among 12,000 records) in addition to above performance question.
* - Reminder: when Multiple Values is set you can assign multiple values to a single product.
This is a great step for performance optimization that tells us which SQL comparison operator to use: "=", "IN" or "FIND_IN_SET".
Equal operator (=) is the fastest, then "IN" and lastly "FIND_IN_SET" performs worse.
With Custom Fields you cannot make such hinting, thus the slowest "FIND_IN_SET" would be used always.
Le's say I assigned certain product a "Brand: Microsoft":
and then wanted to change "Brand" to "Sony", I wouldn't be able to do that with CF:
Only way is to delete everything and reassign all values.
Doesn't sound like a good entertainment and moreover this may pollute your Database with junk.
That is why I want to share some thoughts on this matter.
As for Joomla, either it is 1.6 or 1.7 or any other future release—it doesn't matter: Joomla is not an obstacle in any way. It's simply a question of changing installer .xml file which is not a problem.
The main question rests with Virtuemart 2.0.
In the new version VM developers completely abandoned good old Product Types in favor of new Custom Fields.
I would like to illustrate that Custom Fields do not seemingly bring any advantages, but on the other hand have quite a few of drawbacks.
Performance
Performance may be a great issue especially for those who have large amount of products.Let's have a comparative example between Product Type and Custom Fields (we will not delve deep into details).
Let us say, we have 2,000 products and 2 Product Types, each has 1,000 products. And each Product Type has 5 Parameters (like: Brand, Screen, Ram, Hdd, OS).
In Virtuemart 1.1 this would be represented in Database with 2 tables: "vm_product_type_1" and "vm_product_type_2" with 6 columns in each: first column is "product_id" and rest five for each parameter.
Naturally, each of two tables would have 1,000 records.
And when performing a search MySQL will look up only among 1,000 records.
Now let's see how Custom Fields would store 2,000 products' filters in Database.
All values are stored in one table "virtuemart_product_customfields". Every Parameter is being inserted as new record, so one product with our 5 parameters will have 6 records: 5 records for parameters plus 1 record for parent-custom-field (the one that stores 5 custom fields as children).
So our 2,000 products will end up as 12,000 records.
Now, from a logical point of view, in which case 50* SQL queries will perform better (look up from 1K or 12K of records)? :)
* - It's just an example for 50 filters that make 50 queries.
Cannot assign multiple filters to a single product
In Virtuemart 1.1 you could assign few values to a single products:
As well as you can in Fast Seller:
With Custom Fields you can choose only one value.
Well you can skip to define Possible Filters and then enter values by hand for each product and then add a semicolon, like: Red;White;Blue
But that is not really efficiently plus we would have to make another operation of collecting all used filters with more SQL query (look up among 12,000 records) in addition to above performance question.
No way to say what kind of Parameter it is
In Virtuemart 1.1 you can choose Parameter Type either it is Short Text or Multiple Values*.* - Reminder: when Multiple Values is set you can assign multiple values to a single product.
This is a great step for performance optimization that tells us which SQL comparison operator to use: "=", "IN" or "FIND_IN_SET".
Equal operator (=) is the fastest, then "IN" and lastly "FIND_IN_SET" performs worse.
With Custom Fields you cannot make such hinting, thus the slowest "FIND_IN_SET" would be used always.
Not able to change previous selection
Another "great feature" of Custom Fields is that you cannot change previously selected filters.Le's say I assigned certain product a "Brand: Microsoft":
and then wanted to change "Brand" to "Sony", I wouldn't be able to do that with CF:
Only way is to delete everything and reassign all values.
Doesn't sound like a good entertainment and moreover this may pollute your Database with junk.
