Optimum organization of information storage in a database for searching strings by an array of keywords

Question

In order to find a row in the table for some unique key (for example, "car"), there are no problems. But how should the data be stored in a table in order to quickly search for data on the array of keywords (for example, "red car" or "car moscow" or "moscow car loan")?

We want to get, in some cases, the rows of the table that have matches by the maximum number of keywords, in other cases - the rows of the table, sorted by the number of matches (that is, first come the lines that have matches for all three keywords from "Moscow ", then - lines that match only two of these words, and so on.

Accepted Answer · 2018-09-15T11:18:51

What you are trying to do is full-text search with rankings.

MySQL has MATCH , but it only works on the columns on which the index is built, for example, FULLTEXT (title,body) . Previously used on MyISAM, now available in Innodb 5.6+ .

 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('москва автомобиль кредит');

Correct solution

Therefore, you should use special solutions: Sphinx, its fork Mandragora, or ElasticSearch.

For Sphinx there is a SQL-like language , the connection also goes through the MySQL interface.

 SELECT * FROM index WHERE MATCH('москва автомобиль кредит');

You like? Use.

More Sphinx chips:

rt-index allows you to update the Sphinx index on the fly. Have you updated the news in MySQL? The second command update rt-index
snippets - "podstvetka" keywords in the found text
faceted search

The tools you offer are indispensable when searching through articles, news, and so on.
But still, the analysis of the text in the table cell entirely is not always rational. For example, I do not know how the search in Evernote is implemented, but in general there you can search for notes only by tags, not by content.
In this case, it is reasonable to save the labels in the database cell in the form of a serialized array, with which it is much easier to work with the text.
но он работает только на MyISAM . Your information is very outdated.
By the quality of the search, however, I do not know if there have been any improvements.
@BokovGleb, I don’t know what is inside Evernote, but it looks like the Lucene Search Index – Server side Lucene search index for user content , on the basis of which, again, made Elasticsearch.
Write them down like :москва:автомобиль:кредит: and look in MySQL via LIKE '%:москва:%' OR ...

Optimum organization of information storage in a database for searching strings by an array of keywords

1 answer 1

Correct solution

More articles: