Quote
the fastest way to do this would be to use MATCH AGAINST. Note the sit_keyword table has a FULLTEXT index.
e.g.
SELECT
*
FROM
sit_keyword sk
WHERE
MATCH(sk.keyword) AGAINST (@large_resume_text);
Problem is, that reverses Match ... Against logic, which is that Match arguments specify text to be searched, and Against arguments specify items to search for.
Without that reversal, matching whole phrases by double-quoting them works as documented.
Unfortunately, to Match Against a table of keywords in MySQL seems to require a cursor, which will be painfully slow.
An alternative would be to assemble a Union query in an API language like PHP.
Edited 2 time(s). Last edit at 12/06/2021 01:05PM by Peter Brawley.