-- Create test table and insert data DROP TABLE IF EXISTS ft_max_test; CREATE TABLE `ft_max_test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `doc_id` int(10) unsigned NOT NULL DEFAULT '0', `title` varchar(255) NOT NULL DEFAULT 'Untitled', `body` mediumtext, PRIMARY KEY (`id`), KEY `doc_id` (`doc_id`), FULLTEXT KEY `title` (`title`), FULLTEXT KEY `body` (`body`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO ft_max_test VALUES (1, 1, 'Insert into table', 'insert into table select from'), (2, 1, 'Delete from table', 'some body text here'), (3, 1, 'Update', 'perform update'), (4, 2, 'Insert into table', 'insert into table select from'), (5, 2, 'Delete from table', 'some body text here'), (6, 2, 'Update', 'perform update'), (7, 3, 'Insert into table', 'insert into table select from'), (8, 3, 'Delete from table', 'some body text here'), (9, 3, 'Update', 'perform update'), (10, 4, 'Insert into table', 'insert into table select from'), (11, 4, 'Delete from table', 'some body text here'), (12, 4, 'Update', 'perform update'); -- Perform FT search getting max relevance for title and body (without using doc_id index) SELECT MAX(MATCH (title) AGAINST ('table' IN BOOLEAN MODE)) AS rel1, MAX(MATCH (body) AGAINST ('table' IN BOOLEAN MODE)) AS rel2 FROM ft_max_test WHERE MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR MATCH (body) AGAINST ('table' IN BOOLEAN MODE); -- +----------------------+---------------------+ -- | rel1 | rel2 | -- +----------------------+---------------------+ -- | 0.031008131802082062 | 0.22764469683170319 | -- +----------------------+---------------------+ -- 1 row in set (0.00 sec) -- View title and body relevance SELECT id, doc_id, MATCH (title) AGAINST ('table' IN BOOLEAN MODE) AS rel1, MATCH (body) AGAINST ('table' IN BOOLEAN MODE) AS rel2 FROM ft_max_test WHERE MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR MATCH (body) AGAINST ('table' IN BOOLEAN MODE); -- +----+--------+----------------------+---------------------+ -- | id | doc_id | rel1 | rel2 | -- +----+--------+----------------------+---------------------+ -- | 1 | 1 | 0.031008131802082062 | 0.22764469683170319 | -- | 2 | 1 | 0.031008131802082062 | 0 | -- | 4 | 2 | 0.031008131802082062 | 0.22764469683170319 | -- | 5 | 2 | 0.031008131802082062 | 0 | -- | 7 | 3 | 0.031008131802082062 | 0.22764469683170319 | -- | 8 | 3 | 0.031008131802082062 | 0 | -- | 10 | 4 | 0.031008131802082062 | 0.22764469683170319 | -- | 11 | 4 | 0.031008131802082062 | 0 | -- +----+--------+----------------------+---------------------+ -- 8 rows in set (0.00 sec) -- Perform FT search getting max relevance for title and body (using doc_id index) SELECT MAX(MATCH (title) AGAINST ('table' IN BOOLEAN MODE)) AS rel1, MAX(MATCH (body) AGAINST ('table' IN BOOLEAN MODE)) AS rel2 FROM ft_max_test WHERE doc_id = 1 AND (MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR MATCH (body) AGAINST ('table' IN BOOLEAN MODE)); -- +------+------+ -- | rel1 | rel2 | -- +------+------+ -- | NULL | NULL | -- +------+------+ -- 1 row in set (0.00 sec) -- Run EXPLAIN to confirm doc_id index was used EXPLAIN SELECT MAX(MATCH (title) AGAINST ('table' IN BOOLEAN MODE)) AS rel1, MAX(MATCH (body) AGAINST ('table' IN BOOLEAN MODE)) AS rel2 FROM ft_max_test WHERE doc_id = 1 AND (MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR MATCH (body) AGAINST ('table' IN BOOLEAN MODE)); -- +----+-------------+-------------+------------+------+---------------+--------+---------+-------+------+----------+-------------+ -- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | -- +----+-------------+-------------+------------+------+---------------+--------+---------+-------+------+----------+-------------+ -- | 1 | SIMPLE | ft_max_test | NULL | ref | doc_id | doc_id | 4 | const | 3 | 20.99 | Using where | -- +----+-------------+-------------+------------+------+---------------+--------+---------+-------+------+----------+-------------+ -- Try to get title and body relevance values for rows matched SELECT id, doc_id, MATCH (title) AGAINST ('table' IN BOOLEAN MODE) AS rel1, MATCH (body) AGAINST ('table' IN BOOLEAN MODE) AS rel2 FROM ft_max_test WHERE doc_id = 1 AND (MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR MATCH (body) AGAINST ('table' IN BOOLEAN MODE)); -- Empty set (0.00 sec) -- Try queries again ignoring the doc_id index ... Works! SELECT MAX(MATCH (title) AGAINST ('table' IN BOOLEAN MODE)) AS rel1, MAX(MATCH (body) AGAINST ('table' IN BOOLEAN MODE)) AS rel2 FROM ft_max_test IGNORE index(doc_id) WHERE doc_id = 1 AND (MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR MATCH (body) AGAINST ('table' IN BOOLEAN MODE)); -- +----------------------+---------------------+ -- | rel1 | rel2 | -- +----------------------+---------------------+ -- | 0.031008131802082062 | 0.22764469683170319 | -- +----------------------+---------------------+ -- 1 row in set (0.00 sec) SELECT id, doc_id, MATCH (title) AGAINST ('table' IN BOOLEAN MODE) AS rel1, MATCH (body) AGAINST ('table' IN BOOLEAN MODE) AS rel2 FROM ft_max_test IGNORE index(doc_id) WHERE doc_id = 1 AND (MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR MATCH (body) AGAINST ('table' IN BOOLEAN MODE)); -- +----+--------+----------------------+---------------------+ -- | id | doc_id | rel1 | rel2 | -- +----+--------+----------------------+---------------------+ -- | 1 | 1 | 0.031008131802082062 | 0.22764469683170319 | -- | 2 | 1 | 0.031008131802082062 | 0 | -- +----+--------+----------------------+---------------------+ -- 2 rows in set (0.00 sec)