[SQLite] FTS table의 활용(FTS5)

photo by unsplash

DB를 구축할 때 단순한 값만 저장하지는 않습니다.

직원이란 테이블에는 이름, 주소, 전화번호의 정보처럼 짤막한 정보들이 담기겠지만, 이메일을 담는다면, 수/발신자, 제목, 내용 등의 내용이 기본적으로 담기겠죠?

이때 이메일의 내용은 단문일 수도, 장문일 수도 있습니다. 이런 이메일 같이 대량의 문장들로 구성된 자료를 DB에서 검색해야 한다면 시간이 오래 걸릴 수밖에 없습니다. 이때 특정 단어를 찾아야 한다면 일반 DB Table일 경우 Like 검색을 진행해야 합니다. 이는 어마어마하게 시간이 많이 걸리겠죠?

이를 위해서 Sqlite에서는 전문검색 (FTS: Full Text Search)라는 virtual table을 제공합니다. 이는 효율적이고 빠르게 문서를 검색하도록 디자인된 Table입니다.

SQLite 공식 페이지에서 [1] 언급된 내용에 따르면 517430개의 email에서 "linux"라는 단어를 검색할 때 일반 table의 LIKE operator 사용과, FTS3/FTS4로 생성된 table에서의 MATCH operator 사용 시 각각 22.5 sec. 0.03 sec.로 비교할 수 없을 만큼의 빠른 속도를 보인다고 합니다.

Overview of FTS5

현재 마지막으로 release 되어 있는 버전이 FTS5 이므로 해당 문서 기준으로 설명합니다. [2]

FTS Table은 오로지 문서 검색을 위해 최적화되어 있습니다. 따라서 생성되는 column은 모두 TEXT 타입만 가능하면, 다른 타입을 명시하더라도 TEXT 컬럼으로 생성됩니다.

또한 CHECK, UNIQUE 등의 제약조건이나, PRIMARY KEY 같은 선언들, ALTER TABLE로 컬럼 추가 등의 작업은 table 생성시점에 에러를 발생시킵니다. 다만 PRIMARY KEY (INTEGER) 역할을 하는 rowid(docid)란 hidden column이 자동으로 추가됩니다.

Table의 생성

CREATE VIRTUAL TABLE email USING fts5(sender, title, body);

생성 방법은 간단합니다. 여러 개의 column을 생성할 수 있으며 생성된 컬럼은 모두 TEXT type으로 생성됩니다. 

이렇게 생성된 Table은 일반 테이블과 동일하게, INSERT, UPDATE, SELECT가 사용 가능합니다. 다만 INDEX나 TRIGGER 등은 생성할 수 없습니다.

MATCH를 이용한 검색

생성된 컬럼에 값을 insert 하면 해당 값들은 지정된(여기서는 지정하지 않았기에 기본 tokenizer가 사용됩니다.) tokenizer에 의해서 따로 indexing이 되기 시작합니다. 이 index를 사용해서 검색을 진행하려면 MATCH operator를 사용하면 되나 그 외에도 아래 명시된 추가적인 두 가지 방법으로도 빠른 검색을 진행할 수 있습니다.

-- Query for all rows that contain at least once instance of the term
-- "fts5" (in any column). The following three queries are equivalent.
SELECT * FROM email WHERE email MATCH 'fts5';
SELECT * FROM email WHERE email = 'fts5';
SELECT * FROM email('fts5');

rank를 이용한 정렬

기본적으로 FTS5 table의 검색에서는 대소문자를 구분하지 않습니다. 또한 기본 SQL에서 사용되는 ORDER BY는 사용하지 않습니다. 반환되는 값은 무작위이며, 검색어와 유사도가 높은 순서대로 정렬하려면 hidden column인 rank를 사용합니다.

-- Query for all rows that contain at least once instance of the term
-- "fts5" (in any column). Return results in order from best to worst
-- match.  
SELECT * FROM email WHERE email MATCH 'fts5' ORDER BY rank;

rank 컬럼에는 검색어와 얼마나 유사성을 갖는지를 숫자로 환산하여 표기합니다. 이때 검색엔진들이 사용하는 OKapi BM25 [3] 알고리즘을 사용하며, 이 알고리즘은 이미 Sqlite에 포함되어 있기 때문에 쉽게 불러서 사용이 가능합니다.

SELECT * FROM email WHERE subject MATCH 'shed*'
AND rank MATCH 'bm25(10.0, 1.0)' ORDER BY rank

*은 wild card로 shed~~ 로 시작되는 단어들을 검색한다는 의미입니다.

Auxiliary functions

검색된 row를 특정 함수를 이용하여 변경된 형태로 반환받을 수 있습니다. 예를 들어 검색된 구문의 특정 부분을 highlight 하고 싶다면 아래처럼 SELECT문을 사용합니다.

-- Query for rows that match "fts5". Return a copy of the "body" column
-- of each row with the matches surrounded by <b></b> tags.
SELECT highlight(email, 2, '<b>', '</b>') FROM email('fts5');

검색된 결과는 highlight function에 의해 <b>~~~</b>로 감싸 져서 반환됩니다. 이런 보조 함수들은 필요한 형태로 직접 C로 구현하여 SQLite core에 등록하여 사용할 수 있습니다.

검색 가능한 형태

FTS5에서는 다음과 같은 형태를 row에서 검색해 낼 수 있습니다.

• 특정한 prefix를 갖는 단어들
• 특정 단어로 구성된 구문이나 특정 prefix로 구성된 구문들
• "NEAR queires": 특정 단어, prefix로 구성된 구문들이 배치된 간격에 따른 검색
• 위 세 가지 형태의 AND, OR, NOT으로 합성한 형태.

FTS5 Table 정보

FTS5는 v3.9.0(2015-10-14)에서 탑재되었습니다. 만약 SQLite가 이전 버전이라면 FTS3, 또는 FTS4를 사용해야 합니다.

FTS table은 빠른 검색을 위하여 inverted index형태의 index를 생성합니다. 따라서 이런 정보들을 저장할 3~5개의 shadow table이 자동으로 추가되어 생성됩니다. 

Table name	Contents
<name>_data	대부분의 full text index data 저장
<name>_idx	위 테이블에 저장되지 않은 나머지 full-text index data를 저장하며, <name>_data table보다 크기가 항상 작다
<name>_config	설정 정보 저장
<name>_content	실제 FTS5에 저장되는 데이터를 담는다. 단, contetless나 external_content 형태의 FTS5 table이라면 생성되지 않음.
<name>_docsize	Virtual table에 존재하는 token들의 각 row의 각 컬럼별 크기 저장. columsize 값을 0으로 하면 생성되지 않음.

 

Full-Text Query

Query를 사용하는 다양한 방법에 대해서 표기합니다. FTS3와 FTS4와는 살짝 다를 수 있습니다.

FTS5 Phrases

"one two three"라는 통 구문을 갖는 문장을 검색하려면 "+" 또는 "."을 써서 문자를 concatnate 할 수 있습니다. 아래 구문들은 모두 동일한 의미입니다.

... MATCH '"one two three"'
... MATCH 'one + two + three'
... MATCH '"one two" + three'
... MATCH 'one.two.three'

FTS Prefix Queries

*을 사용하여 prefix만으로 matching 되는 데이터를 검색할 수 있습니다.

... MATCH '"one two thr" * '
... MATCH 'one + two + thr*'
... MATCH '"one two thr*"'      -- May not work as expected!

위 두 구문은 정상 동작 하지만 마지막 구문은 wild card인 "*"가 큰 따옴표 안에 있기 때문에 tokenizer에 의해서 해서 되지 못합니다.

FTS Inital TOken Queries

"^"를 사용하면 컬럼의 시작이 해당 문구이어야 한다는 표기입니다.

• value1: "one two three four..."
• value2: "1 2 3 4 one tow three four.."

두 개의 값이 table에 존재할 때 value1만 검색하기 위해서는 아래와 같은 형태로 MATCH를 구성합니다.

... MATCH '^one'              -- first token in any column must be "one"
... MATCH '^ one + two'       -- phrase "one two" must appear at start of a column
... MATCH '^ "one two"'       -- same as previous 

"a"라는 특정 컬럼에서 "two...."로 시작되는 컬럼을 검색하려면 아래와 같이 구성합니다.

... MATCH 'a : ^two'          -- first token of column "a" must be "two"

NEAR query를 뒤에 나오겠지만 아래와 같이 구성하면 syntax error가 발생합니다.

... MATCH 'NEAR(^one, two)'   -- syntax error! 
... MATCH 'one + ^two'        -- syntax error! 
... MATCH '"^one two"'        -- May not work as expected!

FTS NEAR Queries

두 개 이상의 구문들을 묶어 NEAR group를 구성할 수 있습니다. NEAR query는 입력된 구문들 간의 간격이 10개(기본값)가 넘지 않을 때 matching 됩니다. 

만약 구문간 간격을 조절하려면 아래와 같이 마지막 param으로 간격을 직접 전달할 수 있습니다.

CREATE VIRTUAL TABLE f USING fts5(x);
INSERT INTO f(rowid, x) VALUES(1, 'A B C D x x x E F x');

... MATCH 'NEAR(e d, 4)';                      -- Matches!
... MATCH 'NEAR(e d, 3)';                      -- Matches!
... MATCH 'NEAR(e d, 2)';                      -- Does not match!

... MATCH 'NEAR("c d" "e f", 3)';              -- Matches!
... MATCH 'NEAR("c"   "e f", 3)';              -- Does not match!

... MATCH 'NEAR(a d e, 6)';                    -- Matches!
... MATCH 'NEAR(a d e, 5)';                    -- Does not match!

... MATCH 'NEAR("a b c d" "b c" "e f", 4)';    -- Matches!
... MATCH 'NEAR("a b c d" "b c" "e f", 3)';    -- Does not match!

FTS5 Column Filters

한 개의 구문을 검색할 때나 NEAR group을 이용한 검색 시 특정 칼럼에 대해서만 검색하도록 제한할 수 있습니다. 만약 검색 대상이 되는 특정 컬럼이 복수개 라면 이를  중괄호 안에 넣고 띄어쓰기로 구분하여 사용합니다. (이때 column명은 대소문자를 구분하지 않습니다.)

... MATCH 'colname : NEAR("one two" "three four", 10)'
... MATCH '"colname" : one + two + three'

... MATCH '{col1 col2} : NEAR("one two" "three four", 10)'
... MATCH '{col2 col1 col3} : one + two + three'

반대로 특정 컬럼에서의 검색만 제외하고 싶을 때는 아래와 같이 "-"를 prefix로 사용하여 표현합니다.

-- Search for matches in all columns except "colname"
... MATCH '- colname : NEAR("one two" "three four", 10)'

-- Search for matches in all columns except "col1", "col2" and "col3"
... MATCH '- {col2 col1 col3} : one + two + three'

두 번째 구문처럼 검색하면 전체 column에서 "one two three" 구문을 검색하지만 col1, col2, col3 컬럼들에서는 검색하지 않습니다.

또한 WHERE 절에 Table 명을 주고 MATCH 부분에 column filter를 적용할 수도 있습니다.

-- Given the following table
CREATE VIRTUAL TABLE ft USING fts5(a, b, c);

-- The following are equivalent
SELECT * FROM ft WHERE b MATCH 'uvw AND xyz';
SELECT * FROM ft WHERE ft MATCH 'b : (uvw AND xyz)';

-- This query cannot match any rows (since all columns are filtered out): 
SELECT * FROM ft WHERE b MATCH 'a : xyz';

FTS5 Boolean Operators

구문 및 NEAR group을 Boolean operator를 이용하여 표현할 수 있습니다.

• <query1> NOT <query2> : query1은 match 되나 query2는 포함되지 않아야 함.
• <query1> AND <query2> : query1과 query2 모두 match
• <query1> OR <query2> : query1 또는 query2 하나만이라도 match 되는 경우

-- Matches documents that contain at least one instance of either "one"
-- or "two", but do not contain any instances of token "three".
... MATCH 'one OR two NOT three'

-- Match all documents that contain the token "two" but not "three", or
-- contain the token "one".
... MATCH 'one OR (two NOT three)'

기본적으로 boolean operator를 사용하지 않고 띄어쓰기를 하면 AND가 생략된 것으로 처리됩니다.

... MATCH 'one two three'         -- 'one AND two AND three'
... MATCH 'three "one two"'       -- 'three AND "one two"'
... MATCH 'NEAR(one two) three'   -- 'NEAR(one two) AND three'
... MATCH 'one OR two three'      -- 'one OR two AND three'

... MATCH '(one OR two) three'    -- Syntax error!
... MATCH 'func(one two)'         -- Syntax error!

 

FTS5 Table Creation and Initailization

위에서는 CREATE VIRTUAL TABLE.. 명령어를 이용하여 가장 기본적인 형태로 FTS 테이블을 만들었지만 추가적인 옵션을 줄 수 있습니다. FTS5에서 컬럼에 줄 수 있는 option으로는 UNINDEXED만 가능하며, 그 외의 값은 모두 에러 처리됩니다. 그 외에 설정 옵션은 option에 "="을 붙여 표기합니다.

UNINDEXED column option

FTS5 column을 만들 때 index가 필요 없는 컬럼에 표기합니다. 이 옵션이 붙은 컬럼은 FTS index에 추가되지 않습니다.

CREATE VIRTUAL TABLE customers USING fts5(name, addr, uuid UNINDEXED);

위와 같이 uuid는 말 그대로 unique 한 문자 배열로 구성됩니다. 따라서 index로 만들 필요가 없기 때문에 UNINDEXED를 붙여 줍니다. 일반적으로 UUID는 partion로 검색하기보다는 전체를 검색하기 때문이죠.

Prefix indexes

기본적으로 FTS5는 문서들에 존재하는 각각의 token에 대한 위치를 저장하여 관리합니다.  이렇게 저장된 index는 하나의 완성된 토큰을 검색할 때는 빠르지만 prefix token을 검색할 때는 range scan을 해야 하기 때문에 다소 느립니다. 예를 들면 "abc*"를 검색하려면 "abc" ~ "abd"의 범위를 검색해야 하기 때문입니다. 따라서 특정한 크기의 prefix의 검색이 잦다면 이를 prefix의 개수에 따른 index를 따로 만들어 range 검색을 줄이면서 검색 속도를 향상 시킬 수 있습니다. 물론 index가 늘어나니 index table의 용량은 커지겠죠?

prefix index를 만드는 방법은 prefix option을 사용하고, 공백으로 prefix 개수를 표현하거나, 여러 개의 prefix option을 추가하여 표현할 수 있습니다. 

-- Two ways to create an FTS5 table that maintains prefix indexes for
-- two and three character prefix tokens.
CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2 3');
CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix=2, prefix=3);

위 두 개의 구문은 prefix 2개짜리 index와 3개짜리 index를 각각 구성하며, 동일합니다. 

Tokenizers

Tokenizer는 문장에서 단어를 어떻게 잘라낼지에 대한 방법을 지정합니다. 보통 문장에서 공백을 단위로 잘라내어 token을 구성하지만 만약 한글에 최적화되었다면 동사를 잘라내면 원형으로 바꾼다거나, 변형된 단어를 원 단어로 변형해서 저장한다거나 할 텐데 아직까지 한글에 최적화된 Tokenizer를 FTS5 자체에서 제공하지는 않습니다. 검색 엔진들에서는 각 언어별로 최적화된 tokenizer들이 존재하지만, SQLite에 언어별 최적화된 Tokenizer가 제공되는걸 바라기는 무리가 있지 않나 싶네요.

기본적으로 제공하는 tokenizer는 아래와 같습니다.

• unicode61: 기본값
• ascii
• porter

위 세 개의 Tokenizer에 대한 구성은 여기서 상세하게 다루지 않습니다. 아무래도 글의 범위를 벗어나는 것 같네요. 자세한 내용을 원하시면 SQLite FTS5 Extension [2]의 4.3 Tokenizer section을 참고 바랍니다.

External Content and Contentless Tables

기본적으로 FTS table을 생성하여 데이터를 넣고 이를 빠르게 검색하기 위해 shadow table에 index 정보를 넣어 사용합니다. 하지만 FTS table은 TEXT 타입만 가지고 있기 때문에 integer나 다른 적합한 타입의 컬럼 추가가 필요하거나, 모든 컬럼의 색인을 할 필요가 없을 때 (UNIDEXED를 사용하면 됩니다만..)에는 적합하지 않습니다. 또한 index나 trigger, 기타 제약사항들은 만들 수 없기에 일반 테이블 대용으로 쓰기엔 적합하지 않습니다.

그렇다고 데이터를 일반 Table에 저장하고, 이를 검색을 하기 위해 동일 테이터를 복제해서 FTS 테이블을 만드는 것 또한 Disk 공간의 낭비를 가져옵니다.

따라서 기본적인 데이터는 기존 테이블에 저장하고 FTS table을 이용하여 index만 생성하는 목적을 위해 SQLite에서는 두 가지 형태를 제공합니다.

CONTENTLESS CONTENT TABLE

Contentless table은 말 그대로 실제 content를 담는 table은 따로 존재하고. FTS5 Table에는 index만 생성하여 사용합니다.

CREATE VIRTUAL TABLE t1 USING fts5(content="", a, b, c);

't1'이라는  FTS table을 만들고 content option을 emtpy string을 지정합니다.

이 테이블에는 데이터가 저장되지 않으며, 오로지 INSERT와 SELECT만 가능합니다. (UPDATE, DELETE 불가)

-- This statement is Ok:
INSERT INTO t1(rowid, a, b, c) VALUES(1, 'a b c', 'd e f', 'g h i');

-- This statement causes an error, as no rowid value has been provided:
INSERT INTO t1(a, b, c) VALUES('j k l', 'm n o', 'p q r');

또한 INSERT 시점에 반드시 rowid를 넣어줘야 합니다. 이렇게 추가된 데이터는 index만 생성되며, 실제 원본 데이터는 저장되지 않습니다. 따라서 아래와 같이 SELECT 구문을 통해 index를 이용한 검색을 진행할 수 있으나, 반환 값으로 a, b, c 컬럼중 하나를 선택한 경우 FTS5에서는 null이 반환됩니다. (FTS4 에서는 error를 발생시킵니다.)

SELECT 구문에는 auxiliary function인 matchinfo()나 rowid만 반환되도록 할 수 있습니다. (FTS4에서는 docid)

-- The following statements are Ok:
SELECT docid FROM t1 WHERE t1 MATCH 'xxx';
SELECT docid FROM t1 WHERE a MATCH 'xxx';
SELECT matchinfo(t1) FROM t1 WHERE t1 MATCH 'xxx';

-- The following statements all cause errors, as the value of columns
-- other than docid are required to evaluate them.
SELECT * FROM t1;
SELECT a, b FROM t1 WHERE t1 MATCH 'xxx';
SELECT docid FROM t1 WHERE a LIKE 'xxx%';
SELECT snippet(t1) FROM t1 WHERE t1 MATCH 'xxx';

따라서 실제 데이터가 저장되는 Table이 따로 존재하고 해당 Table의 rowid와 함께 FTS table에 insert 한 후에 검색은 FTS 테이블에서 하고 반환반은 rowid를  원본 table과 join 해서 실제 데이터들을 보여주도록 사용할 수 있습니다.

EXTERNAL CONTENT TABLE

External content 테이블은 contentless table과 다르게 외부의 데이터를 참조해서 보여줍니다. index만을 생성하여 저장하는 용로도 FTS table을 사용하지만 참조하는 table을 명시함으로써, SELECT로 조회 시 실제 데이터들도 출력하도록 할 수 있습니다. (물론 해당 데이터는 참조되는 테이블에서 가져와서 반환해 줍니다.)

CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
CREATE VIRTUAL TABLE t3 USING fts5(content="t2", b, c);

t2라는 실제 데이터를 저장하는 Table을 생성하고, 이 테이블을 참조하도록 FTS Table을 만듭니다. 이때 fts5에서 index를 생성할 원본 테이블을 content option에 지정하고, indexing 대상이 되는 원본 컬럼과 동일한 이름으로 FTS table에 컬럼을 생성합니다.

INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f');
INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l');
INSERT INTO t3(rowid, b, c) SELECT id, b, c FROM t2;

Test를 위해 t2 Table에 데이터를 집어넣고, t3에도 t2에 넣었던 데이터를 동일하게 insert 합니다.

select * from t2;
select * from t3;

한 결과는 아래와 같습니다.

Contentless table과는 다르게 FTS에서 실제 데이터를 반환하도록 아래와 같이 SELECT를 할 수 있습니다.

SELECT * FROM t3 WHERE t3 MATCH 'k';

 

'c' column에 'k'가 포함되어 있기 때문에 해당하는 컬럼이 나옵니다.

만약 원본 테이블을 아래와 같이 'k'가 없도록 수정한 후에 동일한 query로 fts table을 조회하면 어떨까요?

-- Following the UPDATE, the query still returns a single row, this
-- time containing the text values "xxx" and "yyy". This is because the
-- full-text index still indicates that the row with docid=3 matches
-- the FTS4 query 'k', even though the documents stored in the content
-- table have been modified.
--
UPDATE t2 SET b = 'xxx', c = 'yyy' WHERE rowid = 3;
SELECT * FROM t3 WHERE t3 MATCH 'k';

원본 테이블의 값은 변경되었지만 검색은 fts table에서 하고 있습니다. 당연히 fts는 변경 전 데이터로 index를 하고 있기 때문에 'k'로 검색 시 rowid = 3을 찾고 이에 해당하는 값을 원본 테이블에서 찾아 출력하므로 'xxx', 'yyy'가 반환됩니다.

이번에는 원본 table을 삭제한 상태에서 fts table을 조회하면 어떻게 되는지 확인해 보겠습니다.

DELETE FROM t2;
SELECT * FROM t3 WHERE t3 MATCH 'k';

원본 테이블('t2')이 삭제되었지만 t3 테이블은 데이터를 그대로 가지고 있기에 여전히 rowid = 3을 찾지만 원본 테이블에 해당 정보가 없기 때문에 FTS4에서는 null을 반환하고, FTS5에서는 errror가 발생합니다.

눈치챘겠지만 처음 원본 테이블인 t2를 만들고, fts 테이블인 t3를 만들 때 이 t2의 값을 그대로 넣었습니다. 서로 rowid를 참조로 가지고 있지만 원본 테이블의 변경이 fts table까지 연결되지 않기 때문에 그대로 사용 시 두 개의 table의 sync가 틀어져 엉뚱한 값이 검색되거나, 에러가 발생할 소지가 생깁니다.

따라서 External Content Table을 사용 시 반드시 원본 테이블의 변경을 FTS에 반영하도록 trigger를 걸어야 합니다.

-- FTS5 Triggers to keep the FTS index up to date.

CREATE TRIGGER t2_bu AFTER INSERT ON tb2 BEGIN
  INSERT INTO t3(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
CREATE TRIGGER tb2_ad AFTER DELETE ON tb2 BEGIN
  INSERT INTO t3(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;
CREATE TRIGGER tb2_au AFTER UPDATE ON tb2 BEGIN
  INSERT INTO t3(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
  INSERT INTO t3(rowid, b, c) VALUES (new.a, new.b, new.c);
END;

만약 FTS4라면 아래와 같이 생성합니다.

-- FTS4 Trigger

CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 BEGIN
  DELETE FROM t3 WHERE rowid=old.rowid;
END;
CREATE TRIGGER t2_bd BEFORE DELETE ON t2 BEGIN
  DELETE FROM t3 WHERE rowid=old.rowid;
END;

CREATE TRIGGER t2_bu AFTER UPDATE ON t2 BEGIN
  INSERT INTO t3(rowid, b, c) VALUES(new.rowid, new.b, new.c);
END;
CREATE TRIGGER t2_bd AFTER INSERT ON t2 BEGIN
  INSERT INTO t3(rowid, b, c) VALUES(new.rowid, new.b, new.c);
END;

 

FTS4에는 모든 index를 삭제하고 rebuild 하는 함수를 제공합니다.

INSERT INTO t3(t3) VALUES('rebuild');

이 함수는 FTS 테이블과 원본 테이블의 충돌로 문제가 있을 때 사용 가능합니다. 또한 원본 테이블이 이미 존재하고 사용되던 상태에서 FTS 테이블을 추가한다면 DB sheme 변경 시점에 호출할 수 있습니다. (단 Contentless table에서 호출 시 에러가 발생합니다.)

Auxiliary Functions

글 상단에서 언급했던 highlight()처럼 추가적인 형태로 제공되는 함수는 Full-text queries에서만 사용된다는 점에서 SQL sclar functions과는 다릅니다. 이외에도 정확도를 측정하는 bm25()이나 snippet() 함수 등이 존재합니다. custom 하게 생성해서 사용할 수 도 있다고 언급했으나, 자세한 내용은 여기서 다루지 않습니다. (잘 사용하지는 않을 것 같아...)

상세한 내용은 SQLite FTS5 Extension [2]의 5.Auxiliary Functions section을 참고하시기 바랍니다.

Conclusion

위에 내용을 요약해 보면 전문 검색(Full Text Search)이 필요할 때는 기존 테이블의 Like operator보다는 FTS Table을 만들어 쓰는 게 훨씬 더 빠르다입니다. 물론 index를 따로 저장해야 되기 때문에 Disk 용량의 손해는 어느 정도 감당해야 할 몫입니다. 한 예시로 맨 처음 언급했던 517430개의 email contents를 FTS 테이블에 저장 시 일반 테이블보다 비교가 불가능한 수준으로 검색 속도가 빨라지지만 1453MB -> 2006MB로 약 38%의 Disk 용량을 더 사용합니다.

SQLite는 이름 그대로 가벼운 database로 주로 Android나 iOS처럼 모바일 기기에서 사용되기 때문에 더욱더 FTS 기능을 사용할 일이 많지 않습니다. 굳이 꼽아 보자면, 메신저 앱, 메시지 앱, 이메일 앱, 사전앱 등 많지 않네요. 다만 적응 사용성으로 인하여 FTS 테이블의 존재 자체를 몰라 검색 속도 향상을 위해 불필요한 노력이 들어가는 일이 없도록 "아 이런 기능이 있었지!!"라고 정도만 머릿속에 저장해 놓으면 좋을 것 같네요.

이 다음으로는 실제 Android에서 어떻게 FTS를 사용해야 하는지에 대한 글을 포스팅하도록 하겠습니다.

References

[1] sqlite.org/fts3.html

[2] www2.sqlite.org/fts5.html

[3] en.wikipedia.org/wiki/Okapi_BM25

[4] www2.sqlite.org/src/file?name=ext/fts3/README.content&ci=tip