フルテキストインデックス

• MySQL

(8.0)

https://techblog.gmo-ap.jp/2020/01/06/mysql-innodb-fulltext-search-tuto/

drop table tab1;
create table tab1(col1 int primary key,col2 varchar(100) );

insert into tab1 values(1,'test1あああ');
insert into tab1 values(2,'test2いいい');
insert into tab1 values(3,'test3ううう');
select * from tab1;

ALTER TABLE tab1 ADD FULLTEXT INDEX ind1 (col2) WITH PARSER ngram;
show warnings;

explain select * from tab1 where match (col2) against ('あああ' in boolean mode);

set global innodb_ft_aux_table="test/tab1";
select * from information_schema.innodb_ft_index_table order by doc_id, position limit 30;

select @@ngram_token_size;

• Oracle

(12cR1)
https://blogs.oracle.com/oracle4engineer/oracle-text-v5

Oracle Text

-- インストール

conn / as sysdba
@/u01/app/oracle/product/12.1.0/dbhome_1/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP NOLOCK

conn CTXSYS/CTXSYS
@/u01/app/oracle/product/12.1.0/dbhome_1/ctx/admin/defaults/dr0defin.sql "AMERICAN"

conn / as sysdba
@/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/dbmsxdbt.sql

-- 動作確認

conn CTXSYS/CTXSYS
grant ctxapp to test;

conn test/test

drop table tab1 purge;
create table tab1(col1 int primary key,col2 varchar2(100) );

insert into tab1 values(1,'test1あああ');
insert into tab1 values(2,'test2いいい');
insert into tab1 values(3,'test3ううう');
commit;
select * from tab1;

exec ctx_ddl.create_preference('lexer1','JAPANESE_VGRAM_LEXER');

select * from CTX_USER_PREFERENCES;

create index ind1 on tab1(col2) indextype is ctxsys.context parameters('lexer lexer1');

explain plan for
select * from tab1 where contains( col2, 'あああ') > 0;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

• PostgreSQL

(11)
pg_bigm

http://www.denzow.me/entry/2017/09/09/093548

yum install https://osdn.net/projects/pgbigm/downloads/72448/pg_bigm-1.2.20200228-1.pg11.el7.centos.x86_64.rpm

vim postgresql.conf
shared_preload_libraries = 'pg_bigm'

sudo systemctl restart postgresql-11
sudo systemctl status postgresql-11

create extension pg_bigm;
\dx

drop table tab1;
create table tab1(col1 int primary key,col2 varchar(100) );

insert into tab1 values(1,'test1あああ');
insert into tab1 values(2,'test2いいい');
insert into tab1 values(3,'test3ううう');
select * from tab1;

create index ind1 on tab1 using gin (col2 gin_bigm_ops);

set enable_seqscan=off;
explain analyze select * from tab1 where col2 like '%あああ%';

• SQL Server

(2019)

drop table tab1;
create table tab1(col1 int not null,col2 varchar(100) );

alter table tab1 add constraint tab1pk primary key(col1);

insert into tab1 values(1,'test1あああ');
insert into tab1 values(2,'test2いいい');
insert into tab1 values(3,'test3ううう');
select * from tab1;

create fulltext catalog ft as default;
create fulltext index on tab1(col2)
key index tab1pk
with stoplist = system;

set showplan_all on;
go
select * from tab1 where contains(col2,'あああ');