[MariaDB discuss] Re: Fulltext index tokenization rules / DOC_ID

14 Sep 2024

      On Thu, Sep 12, 2024 at 12:47 PM wknauf--- via discuss <
discuss@lists.mariadb.org> wrote:
...
Question 1: this was changed by https://jira.mariadb.org/browse/MDEV-20797
Question 2: still open. I asked the same here:
https://stackoverflow.com/questions/78969640/mariadb-fulltext-index-tokeniza...
To answer your question 2:

If an FTS_DOC_ID column is not defined, InnoDB automatically adds a hidden
FTS_DOC_ID column when the full-text index is created.
If you create a full-text index at CREATE TABLE time and do not specify an
FTS_DOC_ID column, InnoDB adds a hidden FTS_DOC_ID column.

Example:

CREATE TABLE t1 (
ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
FULLTEXT(title)
) ENGINE = InnoDB;

INSERT INTO t1(title) VALUES('mysql');
INSERT INTO t1(title) VALUES('database');
....
SELECT * FROM t1;
ID title
1 mysql
2 database
SET GLOBAL innodb_ft_aux_table="test/t1";

SELECT WORD, DOC_ID FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
WORD   DOC_ID
database  3
mysql       2

Here the hidden FTS_DOC_ID value is 2 and 3 for mysql and database. It
doesn't have to match with ID column in the table.

Another way to create your own FTS_DOC_ID column, the column must be
defined as BIGINT UNSIGNED NOT NULL and named FTS_DOC_ID (all uppercase)

Example:

CREATE TABLE t1 (
        FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        FULLTEXT(title)
) ENGINE = InnoDB;
INSERT INTO t1(title) VALUES('mysql');
INSERT INTO t1(title) VALUES('database');
.....
SELECT * FROM t1;
FTS_DOC_ID title
1 mysql
2 database

SET GLOBAL innodb_ft_aux_table="test/t1";
SELECT WORD, DOC_ID FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
WORD DOC_ID
database 2
mysql      1
SET GLOBAL innodb_ft_aux_table=default;

Here FTS_DOC_ID for mysql and database is "1" and "2"

FTS_DOC_ID doesn't have to be PRIMARY KEY. It can be part of UNIQUE INDEX
as well

Example:

CREATE TABLE t1 (
ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
UNIQUE INDEX FTS_DOC_ID_INDEX (FTS_DOC_ID),
title VARCHAR(200),
FULLTEXT(title)
) ENGINE = InnoDB;

INSERT INTO t1(FTS_DOC_ID, title) VALUES(1, 'mysql');
INSERT INTO t1(FTS_DOC_ID, title) VALUES(2, 'database');

SELECT * FROM t1;
ID FTS_DOC_ID title
1 1 mysql
2 2 database

SET GLOBAL innodb_ft_aux_table="test/t1";
SELECT WORD, DOC_ID FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
WORD DOC_ID
database 2
mysql      1

-- 
Thirunarayanan B
Software Engineer
MariaDB

[MariaDB discuss] Re: Fulltext index tokenization rules / DOC_ID

Thirunarayanan Balathandayuthapani