0

I have two tables of

CREATE TABLE titles
(
title_id int(11) unsigned NOT NULL AUTO_INCREMENT,
title varchar(255),
INDEX(title),
PRIMARY KEY(title_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

CREATE TABLE phrases
(
phrase_id int(11) unsigned NOT NULL AUTO_INCREMENT,
phrase varchar(255),
INDEX(phrase),
PRIMARY KEY(phrase_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

I JOIN to find titles containing phrases as

SELECT a.title,b.phrase_id FROM
    titles a JOIN phrases b 
    ON a.title RLIKE CONCAT('\\b',b.phrase,'\\b')

since RLIKE does not use an index, it needs a full table scan, which is obviously slow.

I increased

join_buffer_size
join_buffer_space_limit

but not much difference.

I wonder if there is any trick to make this query faster.

2
  • 1
    Switch to a FULLTEXT index. Much faster, no need to fabricate regexps.
    – Rick James
    Commented Aug 1, 2022 at 23:00
  • @RickJames, it is not a frequent query. In fact, I have a table storing title,phrase_id. I run INSERT INTO SELECT rarely when titles or phrases is changed. Therefore, it is not worth FULLTEXT.
    – Googlebot
    Commented Aug 1, 2022 at 23:25

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.