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.
FULLTEXT
index. Much faster, no need to fabricate regexps.title,phrase_id
. I runINSERT INTO SELECT
rarely whentitles
orphrases
is changed. Therefore, it is not worthFULLTEXT
.