I'm using a Configurable Product methodology in my system. Everything works great, but I have one thing I think that might strongly impact the performance in the future.
This is a dynamic linking table to link the Values of Properties to the Products (that are Variants based on their parent Product (that is on it's on a Configurable Product):
So this is pretty basic of course. For now the field is max-length
VARCHAR (UTF-8). I'm not really sure if such a long
VARCHAR will gain search-perfomance over
TEXT already, but I did it anyway for now.
The system works solid and as simple as this table might seem, it can't be dispatched to broken tables for each property, as the product's variant can have infinite dimensions, all of them based on Properties not yet known.
My question is how I can optimize this table (mainly the
value) so it will have the least performance loss in the future, when the table grows in size. It will probably never get over a few million. I'm curious about indexing and alternatives to the
value's field type.
Not to worry tho; the values will never get the contain weird BLOB-data, as all media goes separately to a Product Assets table (and even there; still as media-links, never very long).