mysql – speeding up JOIN by LIKE or REGEXP

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.

Tags