안녕하세요.

json 포맷의 로그들을 mysql의 text 필드에 저장하여 관리하고 있습니다.

해당 필드는 full text index가 지정되어 있는데요, 검색 되는 단어에 따라서 빠르게 조회가 되기도 하고,

혹은 조회가 되지 않고 타임아웃이 걸려 커넥션이 끊어지기도 합니다.

하여 어떻게 개선을 하면 좋을지 문의 드립니다.

CREATE TABLE log_data (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `log_id` varchar(45) NOT NULL,
  `log_type` varchar(45) NOT NULL,
  `logs` text,
  `reg_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `log_data_reg_dt_index` (`reg_dt`),
  FULLTEXT KEY `log_data_logs_fulltext_index` (`logs`)
);

SELECT 
	id,
    market_type,
    log_id,
	log_type,
    logs,
    reg_dt
FROM log_data
	reg_dt between '2022-01-01T00:00:00' and '2022-05-12T16:25:23'
 AND
	match(logs) against('2720101839')
;

처음엔 위에 등록된 쿼리 처럼 match against를 사용하였습니다. 큰 특이사항 없이 빠르게 조회가 되었습니다.

SELECT 
	id,
    market_type,
    log_id,
	log_type,
    logs,
    reg_dt
FROM log_data
	reg_dt between '2022-01-01T00:00:00' and '2022-05-12T16:25:23'
 AND
	match(logs) against('test.query.category')
;

그런데 위의 쿼리처럼 검색 조건에 숫자가 아닌 문자열 데이터가 들어가게 되는 경우

조회 속도가 급격히 느려 지게 되었습니다.

SELECT 
	id,
    market_type,
    log_id,
	log_type,
    logs,
    reg_dt
FROM log_data
	reg_dt between '2022-01-01T00:00:00' and '2022-05-12T16:25:23'
 AND
	logs like '%test.query.category%'
;

하여 위의 형태로 like 문으로 변경을 하였더니 다시 속도의 개선이 있었습니다만.

숫자형 데이터를 like문에 넣으니 느려서 조회가 되지 않았습니다.

결론적으로 보면,

숫자형 검색값의 경우 match against를 쓰면 빠르게 검색이 되었고,

문자열 검색값의 경우 like를 쓰면 빠르게 검색이 되었습니다.

서로 반대의 경우에는 굉장히 느려졌습니다.

하여 이러한 이유가 발생하는 이유가 궁금하기도 하고,

이런 경우에 어떤 형태로 쿼리를 조회하는 기능을 만들면 좋을지 조언을 구하고 싶습니다.

현재는 조건을 파싱하여, int형의 데이터인 경우 match agains 쿼리를,

그 외에는 like 문 쿼리를 구성하여 조회하는 방식을 생각 하고 있고,

혹시 1개의 쿼리로 둘다 가능하다면 1개의 쿼리로 구성을 하고 싶은 니즈도 있습니다.

아무쪼록 조언을 부탁 드리겠습니다.