MySQL: FULLTEXT 인덱스와 일반 인덱스 성능 차이

발생일: 2018.07.26

키워드: fulltext index, use index, match against

문제:

FULLTEXT 인덱스를 적용해 둔 테이블에서 날짜와 주소로 조회하려고 한다.

테이블 크기는 약 1800만 행 정도이고, 컬럼과 인덱스 정보는 아래와 같다.

employee: 약 1800만 행

- address: 주소

- join_date: 입사일

인덱스:

- address: FULLTEXT index

- join_date: index

주소와 날짜로 검색하기 위해 아래와 같이 조회했는데,.. 엄~청 느리다.

SELECT *

FROM employee

WHERE

    MATCH(address) AGAINST('+서울' IN BOOLEAN MODE) -- (A)

    AND join_date >= '${fromDate}' -- (B)

    AND join_date <= '${now}'

왜 그런 걸까?

해결책:

풀텍스트 인덱스(A 조건)가 날짜 인덱스(B 조건)보다 먼저 적용됐기 때문이다.

FULLTEXT 인덱스는 전체 테이블을 대상으로 스캔하고, 매칭 결과도 많아 오래 걸린 것이었다.

이번 건의 경우, 날짜 조건이 더 좁았기 때문에 USE INDEX로 입사일 인덱스를 사용하게 변경했다.

SELECT *

FROM employee USE INDEX (join_date)

WHERE

    address LIKE '%서울%'

    AND join_date >= '${fromDate}'

    AND join_date <= '${now}'

참고로, FULLTEXT 인덱스는 다른 인덱스와 함께 사용할 수 없다.

MATCH AGAINST 구문을 인덱스 설정 없이 사용하면 아래와 같은 오류가 발생한다.

[HY000][1191] Can't find FULLTEXT index matching the column list

참고: 

http://jojoldu.tistory.com/243

http://blog.acronym.co.kr/270