You need to do something similar to an online store. The problem is in choosing the method of storing information about the goods, as there are more than a hundred types of goods and each of them has its own set of attributes.
The same Magento Commerce for these purposes uses the EAV structure, due to which select requests become very complicated and do not work very quickly. So far, the only thought is to periodically convert EAV into a normal table, from which samples will be drawn. Is there a more efficient way within MySQL or PostgreSQL?
Also in the process of searching for an answer to this question, I repeatedly met the opinion that in this case it is worth refusing the relational base and switching to noSQL. How true is this and will it work faster?
1. Memory tables - fast random seek, limited size and the need to synchronize with real data.
2. Sphinx and others like him. Additionally, we store the data in a format more convenient for faceted search.
3. nosql - easier to scale, data is often stored in memory, the lack of a scheme, but with the ability to create indexes on the fields.
If you need guaranteed speed - look towards the facet search using Sphinx or Lucene / Solr. The implementation will not be the most convenient (although perhaps there are already some beautiful wrappers), but the speed is much higher. But note that the search in this case is done according to predetermined ranges - a request like "price from $ 42.34 to $ 123.45" will not work. - tamar agatha kapanadze
will do about 100 random seek blockquote>
Strange statement. If you take the same MySQL, then you will have a sample, for example, after one pass through each of 2 tables (Entity and Attribute) with indices. Those. 100 fields for 2 passes on indices. - lemmy
lack of a scheme, but with the possibility of creating indexes on the fields blockquote>
Depends on the DBMS. Somewhere you can. - cheeriolafs