SQL - MySQL, Full Text Index

MySQL

 

 

✅ Full Text Index란?

---

문장(텍스트) 안에서 단어를 검색하기 위한 인덱스

LIKE는 문자열 비교
FULLTEXT는 “검색 엔진 방식”

 

 

✅ Usage

---

Natural Language Mode

주어진 text와 연관성이 높은 결과를 찾는 방식

의미있는 결과를 찾기 위해 the, a 같은 불용어는 검색에서 제외됨

CREATE FULLTEXT INDEX idx_overview ON movies (overview);

SELECT
	title,
	overview
FROM
	movies
WHERE
	MATCH(overview) AGAINST ('the food');

 

 

Boolean Mode

• 특수 문자(Operator):
▫ ‎`+` : 해당 단어가 반드시 포함되어야 함 (AND)
▫ ‎`-` : 해당 단어가 반드시 포함되면 안 됨 (NOT)
▫ 아무 단어 : 해당 단어가 있으면 점수(순위)가 올라감 (OR)
▫ ‎`*` : 와일드카드(접두어 검색, 예: apple* → apples, applesauce 등)
▫ ‎`"` : 정확한 구문(phrase) 검색
▫ ‎`~` : 해당 단어가 있으면 점수가 낮아짐(soft NOT)
▫ ‎`>` / ‎` ▫ ‎`()` : 그룹화(서브쿼리)

CREATE FULLTEXT INDEX idx_overview ON movies (overview);

SELECT
	title,
	overview,
	MATCH(overview) AGAINST ('+revenge -violence' IN BOOLEAN MODE) AS score
FROM
	movies
WHERE
	MATCH(overview) AGAINST ('+revenge -violence' IN BOOLEAN MODE);

CREATE FULLTEXT INDEX idx_overview ON movies (overview);

SELECT
	title,
	overview,
	MATCH(overview) AGAINST ('+travel >asia <europe' IN BOOLEAN MODE) AS score
FROM
	movies
WHERE
	MATCH(overview) AGAINST ('+travel >asia <europe' IN BOOLEAN MODE);

travel은 필수 키워드, asia 포함되면 가점, europe 포함되면 감점

 

CREATE FULLTEXT INDEX idx_overview ON movies (overview);

SELECT
	title,
	overview,
	MATCH(overview) AGAINST ('+(action, thriller) -horror +love' IN BOOLEAN MODE) AS score
FROM
	movies
WHERE
	MATCH(overview) AGAINST ('+(action, thriller) -horror +love' IN BOOLEAN MODE);

action과 thriller 둘 중 하나는 무조건 포함

 

CREATE FULLTEXT INDEX idx_overview ON movies (overview);

SELECT
	title,
	overview,
	MATCH(overview) AGAINST ('psycho*' IN BOOLEAN MODE) AS score
FROM
	movies
WHERE
	MATCH(overview) AGAINST ('psycho*' IN BOOLEAN MODE);

psycho 뒤에 아무 글자나 와도 되는 단어가 포함

 

Query Expansion Search

해당 쿼리로 검색된 결과에서 연관성을 찾고 해당 연관성으로 다시 쿼리 검색을 한다.
검색을 2번하기 때문에 리소스가 많이 든다.(그리고 검색하는 범위가 넓어져서 결과도 많이 나오게 됨)

CREATE FULLTEXT INDEX idx_overview ON movies (overview);

SELECT
	title,
	overview,
	MATCH(overview) AGAINST (
		'kimchi'
		WITH
			QUERY EXPANSION
	) AS score
FROM
	movies
WHERE
	MATCH(overview) AGAINST (
		'kimchi'
		WITH
			QUERY EXPANSION
	);

 

https://dev.mysql.com/doc/refman/8.4/en/fulltext-boolean.html

MySQL :: MySQL 8.4 Reference Manual :: 14.9.2 Boolean Full-Text Searches