There is a table with announcements of goods washing malishn.

+----+-----------------+----------------+ | id | brand_name | model | +----+-----------------+----------------+ | 1 | brand1 | model1 | | 2 | brand2 | model2 | | 3 | brand3 | model3 | +----+-----------------+----------------+

Each washing machine has its own characteristics: type of loading, presence of drying, weight, color, etc. (about 40-50 for each of the models).

I am doing a filter on the site. I need to choose certain ads of washing machines that have, for example, only black and white color and with the presence of drying.

The question is how to store these characteristics in the database, so that it would be faster to search ?

The first thought that came to mind: there are a lot of characteristics for each ad, you need to serialize an array in PHP and write its table with the ads. And then search for the regular expression by this field.

The second option is to create a second table and write down all the specifications there. When filtering, the main JOIN is the one where the characteristics are stored and for each ad, select based on the characteristics selected by the user.

Tell me, please, how to be, so that the sample from the database was faster and more efficient?

Thank you in advance!

  • one
    You need to serialize an array in PHP and write its table with declarations. The second part of the idea is quite normal - if by serialization we mean the JSON format. MySQL is quite able to work with it, incl. and index if the version is not very outdated. But the first part of the pumped up - well, why do somewhere, except for the database server, the fact that he himself knows how well? - Akina
  • one
    Usually there are two opposing tasks - a quick search for a given parameter and a quick display of all parameters for a single object. In the first case, 2-3 tables are required in the database, organizing the structure of EAV (in Google and on this site you will find many interesting things on these letters). To solve the second problem, it is better when everything is in one place, for example in JSON. For the ideal case, I would put everything in EAV and, when writing there, I would automatically create and save the json option for the goods. And if the base was for example postgresql, and not mysql, then an indexed search would be possible there right in json - Mike

3 answers 3

The fastest way to search for such a database is if each property has its own column. For example, a column for color, a column for the type of load, for the presence of drying, weight, and so on. Of course, such a search for any large amounts of data will be fast only if you have indexes. Index compilation is a separate large topic that should be disclosed with specific examples of slow queries. Creating indexes in advance, without yet having an understanding of what people will look for, may not make sense.

Storing the characteristics in a separate table is not the worst option, but you need to keep in mind that you can only do a limited number of JOIN , and here you can exchange difficulties when adding columns on complexity when creating an SQL query. Such an exchange does not necessarily make sense. We are talking about a table of the form:

 | row_id | model_id | property_id | property_value | |--------|-------------|-------------|----------------|

Such an approach may have problems when sorting, or you will need to have several such tables for different types of data, or duplicate data in empty columns.

    If the data has a constant predetermined structure and set of characteristics, then of course stored in tables. Be sure to set the indexes. especially if the characteristics are many.

    if the data have a NOT known structure in advance, then it can be perverted. but it is better to use some built-in tools of the "work with XML" database engine.

    universal storage - the most time-consuming option to implement. the key point is how users will indicate what exactly they need to find and how they will be able to interpret the resulting set of records.

      In my opinion, you should have an additional table, where all the characteristics for which you need to search in a separate field will be stored.

      This field will be stored as text (a set of words or sentences), to build a full-text index on this field.

       CREATE TABLE search_article ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, article_id INT UNSIGNED NOT NULL REFERENCES article(id), txt TEXT(500), FULLTEXT idx (txt) ) ENGINE=InnoDB;

      Now you can search as follows:

       SELECT * FROM search_article WHERE MATCH ( txt ) AGAINST ('+стиральная +белая +сушильная' IN BOOLEAN MODE)