mysql full text wildcard search like regex

Question

Upto now I have been using

WHERE col REGEXP 'IN (.*) WE TRUST'

But ever since adding the full index search to this column then this query is very slow.

I'm waning to know know how I could implement a wildcard search using full text index searches.

These are two queries that I have been playing with but still Im getting lots of results that is unexpected and not sure at all why my query is pulling those results.

WHERE MATCH (markIdentification) AGAINST ('IN (.*) WE TRUST')
WHERE MATCH (markIdentification) AGAINST ('+IN (.*) +WE +TRUST')
WHERE MATCH (markIdentification) AGAINST ('+IN * +WE +TRUST')

These are the only ones that seem to get even close. Any suggestions?

Thank you

Update for question ref:

SELECT * from table
 WHERE MATCH (col) AGAINST ('+IN * +WE +TRUST')
   AND col LIKE '%IN (.*) WE TRUST%'

Is this correct? If not then how would you do it?

Use `MATCH()` to get a list of candidates and then `LIKE` to whittle them down. — Gordon Linoff, Oct 10 '18 at 11:35
when you say a list of candidates. you mean to use the match as I am above then then another add on query clause. Ill update the question to confirm. Thanks for your speedy response — TheMan68, Oct 10 '18 at 11:38
I mean, use `MATCH()` in a subquery. Then use `LIKE` in the outer query. — Gordon Linoff, Oct 10 '18 at 12:17

O. Jones · Accepted Answer · 2018-10-10T12:27:15.907

4

The FULLTEXT search engine ignores words shorter than three characters. You can set the innodb_ft_min_token_size option to change that, then regenerate your FULLTEXT indexes.

The + (and -) syntax in AGAINST are boolean search mode things. So to use + you need

WHERE MATCH (markIdentification) AGAINST ('+IN +WE +TRUST' IN BOOLEAN MODE )

BOOLEAN mode has lots of special characters to control searches, but * standing alone is not one of them. You can say 'TRUST*' to match trust, trustee, and trusted.

Taking Gordon's suggestion, you might try this:

WHERE MATCH (markIdentification) AGAINST ('+IN +WE +TRUST' IN BOOLEAN MODE )
  AND  markIdentification REGEXP 'IN (.*) WE TRUST'

This will use your FULLTEXT index to look for possible matches, and REGEXP to get more exact results. The expensive REGEXP operation, then, can run on many fewer rows.

(Beware IN NATURAL LANGUAGE MODE when your tables don't have many rows. It can give strange results. The indexer decides which words are too common to bother with, and if you have a small number of words, that decision gets distorted.)

edited Oct 10 '18 at 12:27

answered Oct 10 '18 at 12:20

O. Jones

103,626
17
118
172

. . Without a subquery, do you know that the `match()` is executed first? – Gordon Linoff Oct 10 '18 at 13:05
No, @GordonLinoff, I don't know for sure. Worth testing. – O. Jones Oct 11 '18 at 00:46
how to do a wildcard search using * to act like "like %trust%" I tried *trust* but its not working, the only case it works is "trust*", so it returns results starts with "trust" but how to get results contains the word "trust" regardless if it i in the end or the beginning? – Moauya Meghari Feb 04 '21 at 13:43
@MoauyaMeghari You'd be wise to ask another question. I don't understand what you want. – O. Jones Feb 04 '21 at 13:55
I search for "trust" word, I want to return results contains the word "trust" not only results started with "trust" – Moauya Meghari Feb 04 '21 at 14:14

mysql full text wildcard search like regex

1 Answers1