There is a table of "phrases" with a field of "phrases". There is a table "filter" with the field "word". (figuratively all in Russian)

How to make a selection of phrases that begin with "D", so that phrases that match any of the words in the "filter" table are excluded from this list of phrases.

select the phrase from the phrase where the phrase like 'Re%' .... and how to screw the filter?

    1 answer 1

    Like that:

    select phrase from phrases where phrase like 'Ре%' and phrase not in( select phrase from phrases p join words w on p.phrase like concat('%', w.word, '%') )
    • Thank. Works. Tell me which is better to add indexes to speed up the outlets? Added separately on the phrase field, separately on the word field. Vseravno request takes 0.6 seconds. Is it worth it to do any more composite indexes for more speed? - alex-v
    • In addition to the phrase index, it’s unlikely to speed up this query. The subquery will not use index search, since the word can be anywhere in the phrase. It could accelerate the change of structure, if the phrases are broken into words, and to keep, besides the whole phrase, the words of which it consists. - msi
    • Still interested in this question: is it possible to make a similar selection of phrases with a filter using full-text search? Is it more efficient and faster? - alex-v
    • I did not work with full-text search. Experiment. - msi
    • @ alex-v, full-text search for this and invented. - orkaan