Optimal mySQL design for 500k+ products with up to 50 specs
A common request.  You are actually a step ahead of most questions tagged with [eav] -- you have thought about &quot;common use patterns&quot; 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