Text match and search on Mysql table column

Question

I have a Mysql table with couple of columns, one column contains search_text VARCHAR. The table is recording data with high frequency and contains millions of records.

I want to search a group of words/texts, which should match from all rows for each or all words. We can pass a date range to restrict a range.

I tried FTS on Mysql, but the response was slow.

Table structure:

CREATE TABLE IF NOT EXISTS `textsearch` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `duration` bigint(20) DEFAULT NULL,
  `timer` datetime DEFAULT NULL,
  `search_text` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title` (`search_text`)
)

text to search:

["Word1", "Word2", "Word3", "combined words"]

query:

SELECT  * FROM textsearch t WHERE MATCH (t.search_text) AGAINST ('word1' IN BOOLEAN MODE)   and t.timer BETWEEN 'date1' AND 'date2';

This will be an array of words/texts. Which need to be searched/matched and for all matches we have to sum the duration column from textsearch table.

Answer 1

You can do using HAVING MATCH AGAINST

+ stands for AND

- stands for NOT

[no operator] implies OR

"some words", is to find rows that contain the exact phrase "some words"

WITH cte as (
  SELECT *
  FROM textsearch t 
  WHERE t.timer BETWEEN '2022-02-01' AND '2022-02-28'
  HAVING MATCH (t.search_text) AGAINST ('+lorem +semper +"Vivamus congue"' IN BOOLEAN MODE)
)
select sum(duration)
from cte;

Demo here

Docs