I facing a problem where I have to design a MySQL database capable of containing +500k products in approx. 1.500 categories, that can hold up to 50 specs. (like size, weight, speed, color, etc).
My goal is to find/use a design that is optimal (fast) for selecting products base on the input of up to 50 specs. Later on, I may implement precached on often use search patterns (specs), when I have collected statistics on that.
As my raw data is in Python (dicts/lists) I was initially thinking of using JSON-objects, but as I see it, this would result in huge overhead and maybe bad search performance. (Actually, I have no experience in using JSON in MySQL)
BTW The easiest would have been using MongoDB, but this is a no-go. I'm also trying to find a way to design it as generic as possible without having product-specific tables and like.
Based on the information above... What design/schema would be the optimal/best, regarding fast search in specs. ? Is EAV (Entity-Attribute-Value) the way to go or is the a better way ?
If anybody knows if this question is answered somewhere, please link to it :-)