How to organize a database for efficient search?

Question

PostgreSQL

There is a table tbl_itemin which the main content is stored:
tbl_item

id -> integer primary key
name -> varchar(250) not null

There is a table with many-to-many keywords associated with it:
tbl_tag

id -> integer primary key
name -> varchar(250) not null

rel_item_tag

id -> integer primary key
item_id -> integer not null
tag_id -> integer not null

And there are several related tables. Some are one-to-many, others are many-to-many.
It is necessary to organize a search for records from the tbl_item table by the value of fields in related tables. Those. the search string can be an author, a section, a keyword, and so on.
And here I think, how it is more rational to organize this business. Making a complex request every time is not an option.
It can make a table with words for searching tbl_searchand enter all sections, authors, keywords, etc. into it according to the schedule. and link it to a table tbl_item. And when searching, look for the desired word in it and return the records associated with it from tbl_item?
So will it be right? Or is the search done in some other way?

Answer 1

The most correct answer is Sphinx ( sphinxsearch.com ). A full-fledged search can only be done on a search engine, on the database itself - this is always for show.
And if you decide within the set conditions, then personally I usually created a View with all the necessary data. This will at least free you from "manual" data addition and consistency tracking.

Answer 2

With a good search, something like this is done, only not word forms are entered, but dictionary forms of the word (lexemes) and the places in which they occur (for searching separately by title, by text, by authors ...). This requires a morphological database of the Russian language (for example, according to the Zaliznyak dictionary ). Often, dictionaries of synonyms and the calculation of the proximity of the position of words in the text are also used.