MySQL Forums
Forum List  »  Full-Text Search

Re: MATCH Keywords with Spaces AGAINST Large Text
Posted by: David Allen
Date: December 22, 2021 10:59AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MATCH Keywords with Spaces AGAINST Large Text
93
December 22, 2021 10:59AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.