How to store the characteristics of goods in the database and search for them?

Question

MySQL

There are many suppliers, their goods must be stored in the database. The difficulty is that the products have many different characteristics and it is impossible to arrange them. At the moment, 1 property has been created for them, in which it is stored as is and the search is done through LIKE.

Tell me, how in theory can you store such characteristics so that you can quickly search for them? at the moment, LIKE is failing. With 6 million goods, the task of calculating the number of goods according to a given characteristic is achieved from a few seconds to tens of seconds.

Answer 1

https://ruhighload.com/%D0%9F%D0%BE%D0%BB%D0%BD%D0...

Answer 2

Are the specifications relevant to the products? That is, one product has only one set of characteristics, or one product can come and be sold with different sets of characteristics, for example, adidas sneakers, color: White, size: 37 and adidas sneakers, color: Blue, size: 39. If the second, more complex case , then we make the following tables
1) Table Properties (id, name, valueType) - here we simply store a list of possible properties
2) Table PropertyValues ​​(id, propertyId, value) - here we store possible values ​​​​for properties that have a non-simple type, then is not a string, not a number, not a boolean, not a date
3) CharacteristicsSet(id, productId, name) - a set of properties for a specific item in the warehouse will be stored here, name will be automatically compiled as a string from the properties and their values ​​specified for the item
4) CharacteristicsValues ​​(chartacteristicSetId, propertyId, valueType, value) - here property values ​​for a particular characteristic will be stored.
For example, we received batches of sneakers with the properties color: white, size: 37th and color: blue, size: 39th. (for example 100 and 50 pieces respectively)
Then our tables will look like this:
Properties :
id: 1, property: 'Color', valueType: 'set'
id: 2, property: 'Size', valueType: 'number'
PropertyValues ​​:
id: 1, propertyId: 1, value: 'White'
id: 2, propertyId: 1, value: 'Red'
id: 3, propertyId: 1, value: 'Blue'
CharacteristicsSet:
id: 1, productId: 777, name : 'Color: white, size: 37'
id: 2, productId: 777, name: 'Color: blue, size: 39'
CharacteristicsValues
​​chartacteristicSetId: 1, propertyId: 1, valueType: set, value: 1(reference to white color )
chartacteristicSetId: 1, propertyId: 2, valueType: number, value: 37
chartacteristicSetId: 2, propertyId: 1, valueType: set, value: 2(blue color reference)
chartacteristicSetId: 2, propertyId: 2, valueType: number, value : 39
Well, in the warehouse table it will be possible to store records in the form:
productId: 777, characteristicsSetId: 1,quantity: 100
productId: 777, characteristicsSetId: 2, quantity: 50
If we do not need a different set of properties for one product, then everything is the same, but we do without the CharacteristicsSet table: and in CharacteristicsValues ​​we refer to the product itself. Accordingly, the entire search will go through one CharacteristicsValues ​​table with indexed fields. For example, to find any products with the color White, we do a search

select * from CharacteristicsValues where propertyId = 1 and value = 1

well, with the corresponding connections according to the tables of characteristics and (or) goods