What is the best way to implement a dynamic set of object attributes with the ability to search by them?

Question

SQL

Actually, the question is: how best to implement a dynamic set of object attributes with the ability to search by them?
I have been developing solutions on classic relational DBMS for some time now and I think in a relational way, although I try to change.
The task of a dynamic attribute set itself is quite simple within the framework of the relational model - a table like
object id | attribute id | value
But the question of the optimality of the structure arises: let's say the maximum value of the text attribute, by which it should be possible to search, is varchar (255).
However, at the same time, integer and fractional attributes can also exist in the system, and God knows what else.
With such a table structure, all of them will have to be entered into varchar (255), which, with a large amount of data, will greatly increase the volume and slow down the search.
You can try to follow the path of extensive optimization and create two or more tables for different types of attributes, or perhaps enter two instead of one “value” field: text and numeric. But I somehow intuitively do not like this solution - I have to greatly complicate the logic of generating search queries.
Tell me, please, is there anything interesting to read on this topic? Maybe NoSQL solutions can open up new ways to solve the problem?
UPD:
read about Mongo. Outwardly, everything is beautiful there - a document, it has an arbitrary set of attributes, you can build indexes. The surface problem is easy to solve. How about inside? How much more profitable is the search in such collections (in terms of resources) than the SQL solution?

Answer 1

MongoDB is good because its indexes are already sorted (and you specify how sorted when creating an index)
PS as an option, see these articles related to using the sphinx search engine to search by attribute values:
Sphinx is not only for searching!
Sphinx is not just for searching! My story (My, in this case does not mean my story)

Answer 2

There is such a template - en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model . Successfully used in Magento and a bunch of smaller projects. At first glance, this is exactly what you need.

Answer 3

key-value will not allow sorting and grouping. If you do not need this, only poix - then you can use it.

Answer 4

I have been thinking about this question for a long time, and it seems to me that the most effective way for ordinary databases like mysql will be a separate table in which fields will be added along with attributes if they need to be searched. And if you do not need it, you can, as you suggested.