Thirunarayanan Balathandayuthapani added a comment - 2024-10-22 11:19 This patch just fixes the optimization during fulltext optimization. Basically, it avoid reading the words that need optimization multiple times. This way it can avoid calling fts_optimize_write() multiple times. Wrote a test case to check how collation behaves in fulltext index: SET NAMES utf8; CREATE TABLE t1 ( f1 INT NOT NULL PRIMARY KEY, f2 VARCHAR(64) COLLATE utf8mb4_general_ci, FULLTEXT ft(f2)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,'antenna'); ALTER TABLE t1 FORCE; INSERT INTO t1 VALUES (2, 'äntenna'), (3, 'äntenna'); DELETE FROM t1 WHERE f1 > 2; SELECT * FROM t1; f1 f2 1 antenna 2 äntenna SET GLOBAL innodb_optimize_fulltext_only=ON; OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize status OK SELECT * FROM t1 WHERE MATCH(f2) AGAINST("antenna" IN BOOLEAN MODE); f1 f2 2 äntenna DROP TABLE t1; SET GLOBAL innodb_optimize_fulltext_only=OFF; This happens only in MariaDB. As discussed with marko , reduce the priority of the issue.

Thirunarayanan Balathandayuthapani added a comment - 2024-10-23 16:02 In fts_optimize_write_word() , InnoDB deletes the existing word in the auxiliary table and adds the optimized entry back into the table. Since it is collation equivalent, delete both the entries from the table and insert only one back into the table. This leads to missing row in optimize table of fulltext index.