[Mysql] MATCH AGAINST 사용(fulltext 검색)

데이터베이스 최적화나 튜닝에 대해서 다뤄본 사람은 like 문에 앞뒤로 % 를 달게 되면 index scan을 안타고 full scan을 타게 되는것을 알고 있을것이다.

 

하지만 정확한 값을 모르기도 하고 내용 중간에 포함된 내용을 검색하려고 한다면 어쩔수 없이 like '%test%' 이런식으로 앞뒤로 묶어서 쿼리를 만들어야한다.

그럴경우에 MATCH AGAINST 를 사용하면 효과적이어서 방법을 소개하려고 한다.

 

먼저 maria db 에서 소개하는 사이트는 아래와 같다.

https://mariadb.com/kb/en/match-against/

MATCH AGAINST

 

사용하기 위해서는 몇가지 조건이 있다.

• MATCH AGAINST 키워드를 사용하려면 테이블에 FULLTEXT INDEX가 추가되어있어야 한다.
• MyISAM 엔진에서는 기본 지원, InnoDB에서는 5.6 버전 이상부터 지원한다.

 

FullText 인덱스 생성하는법

CREATE TABLE TEST ( --    ...이상 이하 생략,     FULLTEXT KEY idx_contents (contents) );

ALTER TABLE TEST ADD FULLTEXT INDEX idx_testcol (testcol);

 

사용법

• "검색어1" OR "검색어2"가 포함된 데이터를 조회
  SELECT * FROM TEST 
  WHERE MATCH(testcol) AGAINST('검색어1 검색어2' IN BOOLEAN MODE);
• "검색어1" AND "검색어2"가 모두 포함된 데이터를 조회
  SELECT * FROM TEST
  WHERE MATCH(testcol) AGAINST('검색어1 +검색어2' IN BOOLEAN MODE);
• "검색어1"이 포함된 데이터 중에 "검색어2"가 들어간 것 제외하여 조회
  SELECT * FROM TEST
  WHERE MATCH(testcol) AGAINST('검색어1 -검색어2' IN BOOLEAN MODE);
• "검색어%"가 포함된 데이터를 조회
  SELECT * FROM TEST
  WHERE MATCH(testcol) AGAINST('검색어*' IN BOOLEAN MODE);
• "검색어1 검색어2"가 포함된 데이터를 조회
  SELECT * FROM TEST
  WHERE MATCH(testcol) AGAINST('"검색어1 검색어2"' IN BOOLEAN MODE);