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

Question

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

Thanks

Answer 1

A common request. You are actually a step ahead of most questions tagged with [eav] -- you have thought about "common use patterns" and how to optimize once you gather that info.

You should be able to make an initial guess at what is most common, put them in columns for efficient searching, put flag-like attributes in a FULLTEXT-indexed column, then throw the rest into a JSON column for testing in the app.

More: http://mysql.rjweb.org/doc.php/eav