Database Development

Optimal mySQL design for 500k+ products with up to 50 specs

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 🙂


Leave a Reply

Your email address will not be published. Required fields are marked *