Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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):

ERD sample]

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).

share|improve this question
    
Do you need such a wide column? Can't you make it smaller, like 200-250? –  ypercube Feb 27 at 21:50
1  
as the table grows later you might want to partition it, but don't pre-optimize now. –  Sam D Feb 27 at 22:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.