MySQL에서 Error Code: 1170 에러 해결하기

문제 발생 상황

movies 테이블의 overview 컬럼에 인덱스를 생성하기 위해 다음과 같은 쿼리를 실행했다.

CREATE INDEX idx_overview ON movies (overview);

하지만 쿼리 실행 후 Error Code: 1170 메세지를 받았다.

Error Code: 1170.

BLOB/TEXT column 'overview' used in key specification without a key length

문제의 원인

BLOB 또는 TEXT 타입의 컬럼에 대한 인덱스를 생성하려고 시도하는 경우 발생하는 에러이다. TEXT나 BLOB 타입은 크기가 매우 커질 수 있기 때문에, MySQL은 이러한 컬럼에 대해 인덱스를 생성할 때 키 길이를 명시해줘야 한다.

해결 방안

두 가지의 방법으로 해결이 가능하다.

1. 부분 인덱스 사용

TEXT 또는 BLOB타입의 컬럼에 인덱스를 생성할 때, 특정 부분이나 길이를 명시해 부분적으로 인덱싱 한다.

1-1. 문자 수 지정

컬럼에서 특정 길이만큼만 인덱스를 생성한다.
너무 긴 길이를 인덱싱하면 오히려 성능이 안 좋아질 수 있으니 주의한다.

-- movies 테이블의 overview 컬럼에 대해 255자까지만 인덱싱
CREATE INDEX idx_overview ON movies (overview(255));

1-2. 특정 부분 지정

문자열 컬럼의 특정 위치의 부분만 인덱스를 생성한다.

-- movies 테이블의 overview 컬럼의 첫 50자와 마지막 50자에 대해 인덱싱
CREATE INDEX idx_overview_part ON movies (SUBSTRING(overview, 1, 50), SUBSTRING(overview, -50));

1-3. 특정 패턴 지정

특정한 패턴이 있는 컬럼의 경우 추출하고 싶은 구간을 지정해 인덱스를 생성한다.

-- users 테이블의 email 컬럼에서 도메인부분만 인덱싱
CREATE INDEX idx_email_domain ON users (SUBSTRING_INDEX(email, '@', -1));

2. FULLTEXT 인덱스 사용

영화 줄거리 요약, 블로그 글과 같이 아주 긴 길이의 문자열을 저장하는 컬럼이라면 FULLTEXT 인덱스를 사용하여 효율적인 조회가 가능하다.
FULLTEXT 인덱스는 다음과 같이 생성한다.

-- movies 테이블의 overview 컬럼에 FULLTEXT INDEX 생성
CREATE FULLTEXT INDEX idx_overview ON movies (overview);

FULLTEXT 인덱스를 이용하면 다양한 방법으로 필요한 데이터를 빠르게 조회할 수 있다. 이에 대한 자세한 내용은 FULLTEXT INDEXES 게시글에서 다루도록 하겠다.

결론

MySQL에서 Error Code: 1170은 TEXT나 BLOB 타입의 컬럼에 인덱스를 생성하려고 할 때 발생한다. 두 타입은 길이가 어느정도로 길어질지 예상하기 어렵기 때문에 아주 긴 데이터가 들어가지 않도록 하기 위해 MySQL이 에러를 일으키는 것이다. 이를 해결하기 위해 부분 인덱스 또는 FULLTEXT 인덱스를 사용해야 한다.

부분 인덱스를 사용하면 TEXT 컬럼의 일부만 인덱싱할 수 있기 때문에, 성능을 최적화하면서도 필요한 인덱스를 생성할 수 있다. 하지만 일부만 인덱싱 한다는 것 자체가 제약이 생긴다고 할 수 있다. 또한 긴 문자열을 그대로 다 저장하도록 설정하면 성능이 나빠질 수 있다.
FULLTEXT 인덱스를 사용하면 대용량의 텍스트 데이터를 다양한 방식으로 효율적인 검색을 할 수 있다.