Re: MATCH Keywords with Spaces AGAINST Large Text

[David Allen]

Hey Peter; sorry for the late response - I've had my hands full on this end.

Thanks for taking a stab at it! Unfortunately, that approach takes more than 5 seconds when my REGEXP takes around 300ms.

I'm just not sure there's any way to make this faster...

Just to refresh, I need to walk our keyword and "sit_name" database (which are words, sometimes with spaces) against a LARGE volume of text (sometimes >1000 characters).

Below is the only thing I've found to work so far...


SELECT
sm.id, sm.sit_name, sm.sit_type_c
FROM
sit_main sm
WHERE
var_text REGEXP CONCAT('\\b',sm.sit_name,'\\b')
AND IF(in_sit_type_c > -1, sm.sit_type_c = in_sit_type_c, 1)
GROUP BY
sm.id

UNION

SELECT
sm.id, sm.sit_name, sm.sit_type_c
FROM
sit_keyword sk
LEFT JOIN
sit_main sm ON sk.sit_main_ref_id = sm.ID
WHERE
var_text REGEXP CONCAT('\\b',sk.keyword,'\\b')
AND IF(in_sit_type_c > -1, sm.sit_type_c = in_sit_type_c, 1)
GROUP BY
sm.id