I am working on a product database in MySQL and the products can be multiple types with different attributes. The number of attributes and there datatype vary. I read about some design patterns (post) used in this situation, but none of them looks promising. I am thinking about mixing them somehow.
Lets say, I have these product types (D - dimension, but could be attribute or entity...):
Product_type1: D1 - int, D2 - int, D3 - string, D4 - date, D5 - string
Product_type2: D1 - int, D2 - string, D3 - date, D4 - int, D5 - string, D6 - int
I store a fix number of attributes of every datatype for a product, let's say 3 int field, 2 date, 2 string. The most important attributes stored here (important regarding indexing and searching). The rest of the attributes are stored in a JSON field or an array format.
Store everything in indexed JSON field and search in the table with 'LIKE' queries.
How fast is a Like query in MySQL when you have a big product database (20 000 - 30 000 products). Is solution 1 better or not?