0

I`m having an issue with Fulltext indexes and trying to use index hints at the same time. This is a sample table:

CREATE TABLE IF NOT EXISTS `products` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `idcategory` bigint(20) NOT NULL,
  `name` text NOT NULL,
  `short_description` text,
  `description` longtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idcategory` (`idcategory`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `products` ADD FULLTEXT INDEX `name_fulltext` (`name`);
ALTER TABLE `products` ADD FULLTEXT INDEX `short_description_fulltext` (`short_description`);
ALTER TABLE `products` ADD FULLTEXT INDEX `description_fulltext` (`description`);

This query runs fine:

SELECT *
FROM products USE INDEX (idcategory)
WHERE idcategory = 1

This query runs fine:

SELECT *
FROM products AS p
WHERE p.idcategory = 1
AND (
    MATCH(p.name) AGAINST ("*test*" IN BOOLEAN MODE)
    OR MATCH(p.short_description) AGAINST ("*test*" IN BOOLEAN MODE)
    OR MATCH(p.description) AGAINST ("*test*" IN BOOLEAN MODE)
)

This query has an error:

SELECT *
FROM products AS p USE INDEX (idcategory)
WHERE p.idcategory = 1
AND (
    MATCH(p.name) AGAINST ("*test*" IN BOOLEAN MODE)
    OR MATCH(p.short_description) AGAINST ("*test*" IN BOOLEAN MODE)
    OR MATCH(p.description) AGAINST ("*test*" IN BOOLEAN MODE)
)

Error: #1191 - Can't find FULLTEXT index matching the column list

I dont understand why the index is not being used and why its looking for FULTEXT index to match. Match what?

From the docs (https://dev.mysql.com/doc/refman/8.0/en/index-hints.html):

For boolean mode searches, index hints with FOR ORDER BY or FOR GROUP BY are silently ignored. Index hints with FOR JOIN or no FOR modifier are honored. In contrast to how hints apply for non-FULLTEXT searches, the hint is used for all phases of query execution (finding rows and retrieval, grouping, and ordering). This is true even if the hint is given for a non-FULLTEXT index.

So what is the issue? It says that the index is honored even if the hint is given for a non-FULLTEXT index in the case of a FULLTEXT search. Am I missing something? Am I missunderstanding the documentation?

Thank you for reading this.

Alex Kuzmov
  • 3
  • 5
  • Not sure about this, but if you set up a single [multiple-column](https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html) index (instead of 3 separate ones), does that still result in an error? As written, I don't think you can specify a single index in the hint because the `WHERE` clause needs/uses all three. – b-frid Nov 09 '20 at 22:39
  • @b-frid you mean I should include all of them in the index hint? – Alex Kuzmov Nov 09 '20 at 22:51
  • See my 'answer' below for clarification on that. As goes indexing on the non-FULLTEXT index/key 'idcategory' while at the same time running these BOOLEAN searches on the other columns, I'm thinking we cannot force (via the explicit use of `USE_INDEX`) an index that does not include those needed to complete said `MATCH / AGAINST` searches. (If I'm off-base here hopefully someone else can weigh in.) – b-frid Nov 09 '20 at 23:31
  • To add to that though, I'm not entirely sure 'idcategory' has an index implicitly established. [Primary keys indeed appear to be implicitly indexed](https://stackoverflow.com/questions/1071180/is-the-primary-key-automatically-indexed-in-mysql), but non-primary keys I'm not so sure. If not, the only reason I can come up with for the fact that your 1st `SELECT` query above does NOT error is that the index request is simply ignored, while in the 3rd `SELECT` it errors because you add the searches that need the other indexes (albeit with slightly inconsistent behavior at that). – b-frid Nov 09 '20 at 23:51
  • I dont think its ignored. Try filling the table with random records and the run an explain on the first query. You`ll see that the records are indeed restricted based on the `idcategory` index. Wheather or not this is by default doesnt matter since in the case where you use MATCH against, its ignored or causes an error. – Alex Kuzmov Nov 10 '20 at 12:27
  • Ok yes good thought to check that. So indexing must be implicit to non-primary keys. I assume when running `EXPLAIN` on the second query there is no such restriction? – b-frid Nov 10 '20 at 14:53
  • There is a restriction on the second query as well. Both queries restrict the records, I`m assuming, based on the idcategory index. However, when adding the index hint, thats when the error happens. Thats basically the mistery. I`ve isolated the problem with this test table and the queries provided. – Alex Kuzmov Nov 10 '20 at 15:13
  • Yes that's the piece I really can't quite understand. As shown in my edited answer below, I went through several different configurations to try to get to the bottom of this, and still don't quite understand why the explicit hint doesn't work to key on `idcategory`, even though it keys on that just fine when no hint is given. – b-frid Nov 10 '20 at 20:44

1 Answers1

0

** Edited 11/10 **

Broke down and started fiddling myself with this. After a bit of research and testing I've put together this set of queries which hopefully will help shed some additional light. (I used dbfiddle. Note the queries that fail are commented out because nothing else will run if left in, at least on dbfiddle.)

Try running these, hopefully the output and comments will best explain. In particular, Query Set 7 & 8 show how to setup the multi-column FULLTEXT INDEX, as I think this may be your ultimate intention here.

-------------------------------- Query Set 1 ------------------------------------
-- Only possible key is `idcategory`, because each FULLTEXT INDEX is for a single 
-- column, but using 3 different MATCH clauses, each for a different column. 
-- (i.e. Can't index into all 3 simultaneously using only a single column index.)
-- Keys/Indexes on `idcategory`.

DROP TABLE IF EXISTS `products`;

CREATE TABLE `products` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `idcategory` bigint(20) NOT NULL,
  `name` text NOT NULL,
  `short_description` text,
  `description` longtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idcategory` (`idcategory`),
  FULLTEXT INDEX `name_fulltext` (`name`),
  FULLTEXT INDEX `short_description_fulltext` (`short_description`),
  FULLTEXT INDEX `description_fulltext` (`description`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `products` (`idcategory`, `name`, `short_description`, `description`)
VALUES (1, 'test1', 'sdesc1', 'desc1'), (1, 'test2', 'sdesc2', 'desc2'), (2, 'test3', 'sdesc3', 'desc3');

EXPLAIN SELECT *
FROM products AS p
WHERE p.idcategory = 1
AND (
    MATCH(p.name) AGAINST ("*test*" IN BOOLEAN MODE)
    OR MATCH(p.short_description) AGAINST ("*test*" IN BOOLEAN MODE)
    OR MATCH(p.description) AGAINST ("*test*" IN BOOLEAN MODE)
);

SELECT *
FROM products AS p
WHERE p.idcategory = 1
AND (
    MATCH(p.name) AGAINST ("*test*" IN BOOLEAN MODE)
    OR MATCH(p.short_description) AGAINST ("*test*" IN BOOLEAN MODE)
    OR MATCH(p.description) AGAINST ("*test*" IN BOOLEAN MODE)
);

-------------------------------- Query Set 2 ------------------------------------

-- Possible keys are `idcategory` and `name_fulltext`. All FULLTEXT INDEXES are 
-- single column, and only 1 MATCH clause [for a single column, `name`] is 
-- specified. Keys/Indexes on `name_fulltext`. (When faced with a choice of 
-- multiple indexes, the optimizer will choose the most selective option [i.e. 
-- that option with the lowest number of rows]).

DROP TABLE IF EXISTS `products`;

CREATE TABLE `products` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `idcategory` bigint(20) NOT NULL,
  `name` text NOT NULL,
  `short_description` text,
  `description` longtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idcategory` (`idcategory`),
  FULLTEXT INDEX `name_fulltext` (`name`),
  FULLTEXT INDEX `short_description_fulltext` (`short_description`),
  FULLTEXT INDEX `description_fulltext` (`description`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `products` (`idcategory`, `name`, `short_description`, `description`)
VALUES (1, 'test1', 'sdesc1', 'desc1'), (1, 'test2', 'sdesc2', 'desc2'), (2, 'test3', 'sdesc3', 'desc3');

EXPLAIN SELECT *
FROM products AS p
WHERE p.idcategory = 1
AND (
    MATCH(p.name) AGAINST ("*test*" IN BOOLEAN MODE)
);

SELECT *
FROM products AS p
WHERE p.idcategory = 1
AND (
    MATCH(p.name) AGAINST ("*test*" IN BOOLEAN MODE)
);

-------------------------------- Query Set 3 ------------------------------------

-- Same as Query Set 2, but explicitly call for INDEX `name_fulltext`. Now only
-- possible key is name_fulltext.

DROP TABLE IF EXISTS `products`;

CREATE TABLE `products` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `idcategory` bigint(20) NOT NULL,
  `name` text NOT NULL,
  `short_description` text,
  `description` longtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idcategory` (`idcategory`),
  FULLTEXT INDEX `name_fulltext` (`name`),
  FULLTEXT INDEX `short_description_fulltext` (`short_description`),
  FULLTEXT INDEX `description_fulltext` (`description`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `products` (`idcategory`, `name`, `short_description`, `description`)
VALUES (1, 'test1', 'sdesc1', 'desc1'), (1, 'test2', 'sdesc2', 'desc2'), (2, 'test3', 'sdesc3', 'desc3');

EXPLAIN SELECT *
FROM products AS p USE INDEX (`name_fulltext`)
WHERE p.idcategory = 1 
AND (
    MATCH(p.name) AGAINST ("*test*" IN BOOLEAN MODE)
);

SELECT *
FROM products AS p USE INDEX (`name_fulltext`)
WHERE p.idcategory = 1
AND (
    MATCH(p.name) AGAINST ("*test*" IN BOOLEAN MODE)
);

-------------------------------- Query Set 4 ------------------------------------
----------------------------------- FAILS ---------------------------------------
-- Same as Query Set 1 but trying to force INDEX `name_fulltext`. Doesn't work
-- because `name_fulltext` only indexes a single column, but MATCH / AGAINST
-- has three different clauses, each specifying a different column. (And hence
-- why the only possible key is `idcategory`, as shown in output of EXPLAIN, 
-- Query Set 1.)

--DROP TABLE IF EXISTS `products`;

--CREATE TABLE `products` (
--  `id` bigint(20) NOT NULL AUTO_INCREMENT,
--  `idcategory` bigint(20) NOT NULL,
--  `name` text NOT NULL,
--  `short_description` text,
--  `description` longtext NOT NULL,
--  PRIMARY KEY (`id`),
--  KEY `idcategory` (`idcategory`),
--  FULLTEXT INDEX `name_fulltext` (`name`),
--  FULLTEXT INDEX `short_description_fulltext` (`short_description`),
--  FULLTEXT INDEX `description_fulltext` (`description`)  
--) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--INSERT INTO `products` (`idcategory`, `name`, `short_description`, `description`)
--VALUES (1, 'test1', 'sdesc1', 'desc1'), (1, 'test2', 'sdesc2', 'desc2'), (2, 'test3', 'sdesc3', 'desc3');

--EXPLAIN SELECT *
--FROM products AS p USE INDEX (`name_fulltext`)
--WHERE p.idcategory = 1
--AND (
--    MATCH(p.name) AGAINST ("*test*" IN BOOLEAN MODE)
--    OR MATCH(p.short_description) AGAINST ("*test*" IN BOOLEAN MODE)
--    OR MATCH(p.description) AGAINST ("*test*" IN BOOLEAN MODE)
--);

--SELECT *
--FROM products AS p USE INDEX (`name_fulltext`)
--WHERE p.idcategory = 1
--AND (
--    MATCH(p.name) AGAINST ("*test*" IN BOOLEAN MODE)
--    OR MATCH(p.short_description) AGAINST ("*test*" IN BOOLEAN MODE)
--    OR MATCH(p.description) AGAINST ("*test*" IN BOOLEAN MODE)
--);


-------------------------------- Query Set 5 ------------------------------------
----------------------------------- FAILS ---------------------------------------

-- Trying to force keying/indexing on `idcategory` instead of `name_fulltext`. 
-- Doesn't work because `idcategory` is not established as a FULLTEXT INDEX, 
-- which is required for MATCH / AGAINST? It keyed on this field when not 
-- explicitly calling USE INDEX and no other index was possible, and when
-- explicitly calling USE INDEX but with no MATCH / AGAINST call, so
-- this behavior seems a bit inconsistent.

--DROP TABLE IF EXISTS `products`;

--CREATE TABLE `products` (
--  `id` bigint(20) NOT NULL AUTO_INCREMENT,
--  `idcategory` bigint(20) NOT NULL,
--  `name` text NOT NULL,
--  `short_description` text,
--  `description` longtext NOT NULL,
--  PRIMARY KEY (`id`),
--  KEY `idcategory` (`idcategory`),
--  FULLTEXT INDEX `name_fulltext` (`name`),
--  FULLTEXT INDEX `short_description_fulltext` (`short_description`),
--  FULLTEXT INDEX `description_fulltext` (`description`)  
--) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--INSERT INTO `products` (`idcategory`, `name`, `short_description`, `description`)
--VALUES (1, 'test1', 'sdesc1', 'desc1'), (1, 'test2', 'sdesc2', 'desc2'), (2, 'test3', 'sdesc3', 'desc3');

--EXPLAIN SELECT *
--FROM products AS p USE INDEX (`idcategory`)
--WHERE p.idcategory = 1 
--AND (
--    MATCH(p.name) AGAINST ("*test*" IN BOOLEAN MODE)
--);

--SELECT *
--FROM products AS p USE INDEX (`idcategory`)
--WHERE p.idcategory = 1
--AND (
--   MATCH(p.name) AGAINST ("*test*" IN BOOLEAN MODE)
--);

-------------------------------- Query Set 6 ------------------------------------
----------------------------------- FAILS ---------------------------------------

-- We can't make `idcategory` a FULLTEXT INDEX of course, because it is a 
-- bigint(20). (Could try making this KEY a text field instead, 
-- see if error persists...)

--DROP TABLE IF EXISTS `products`;

--CREATE TABLE `products` (
--  `id` bigint(20) NOT NULL AUTO_INCREMENT,
--  `idcategory` bigint(20) NOT NULL,
--  `name` text NOT NULL,
--  `short_description` text,
--  `description` longtext NOT NULL,
--  PRIMARY KEY (`id`),
--  KEY `idcategory` (`idcategory`),
--  FULLTEXT INDEX `idcategory_fulltext` (`idcategory`) 
--) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--INSERT INTO `products` (`idcategory`, `name`, `short_description`, `description`)
--VALUES (1, 'test1', 'sdesc1', 'desc1'), (1, 'test2', 'sdesc2', 'desc2'), (2, 'test3', 'sdesc3', 'desc3');

--EXPLAIN SELECT *
--FROM products AS p USE INDEX (`idcategory`)
--WHERE p.idcategory = 1 
--AND (
--    MATCH(p.name) AGAINST ("*test*" IN BOOLEAN MODE)
--);

--SELECT *
--FROM products AS p USE INDEX (`idcategory`)
--WHERE p.idcategory = 1
--AND (
--    MATCH(p.name) AGAINST ("*test*" IN BOOLEAN MODE)
--);

-------------------------------- Query Set 7 ------------------------------------

-- A multi-column index. The MATCH / AGAINST search functions like an 'OR' 
-- clause for all three columns. (i.e. if the text pattern is present in any
-- of the three columns, a match is found). Must specify all three columns in MATCH
-- clause, because the FULLTEXT INDEX includes all three.
-- Keys/Indexes on 'name_sdesc_desc_fulltext` (multi-column fulltext index),
-- but shows `idcategory` as possible index.
-- (Perhaps when all is said and done this is the desired behavior?)

DROP TABLE IF EXISTS `products`;

CREATE TABLE `products` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `idcategory` bigint(20) NOT NULL,
  `name` text NOT NULL,
  `short_description` text,
  `description` longtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idcategory` (`idcategory`),
  FULLTEXT INDEX `name_sdesc_desc_fulltext` (`name`, `short_description`, `description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `products` (`idcategory`, `name`, `short_description`, `description`)
VALUES (1, 'test1', 'sdesc1', 'desc1'), (1, 'test2', 'sdesc2', 'desc2'), (2, 'test3', 'sdesc3', 'desc3');

EXPLAIN SELECT *
FROM products AS p
WHERE p.idcategory = 1
AND (
    MATCH(p.name, p.short_description, p.description) AGAINST ("*test*" IN BOOLEAN MODE)
);

SELECT *
FROM products AS p
WHERE p.idcategory = 1
AND (
    MATCH(p.name, p.short_description, p.description) AGAINST ("*test*" IN BOOLEAN MODE)
);

-------------------------------- Query Set 8 ------------------------------------

-- Same as Query Set 7, but explicitly calls for `name_sdesc_desc` FULLTEXT INDEX.
-- Now, only possible index is `name_sdesc_desc_fulltext`.

DROP TABLE IF EXISTS `products`;

CREATE TABLE `products` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `idcategory` bigint(20) NOT NULL,
  `name` text NOT NULL,
  `short_description` text,
  `description` longtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idcategory` (`idcategory`),
  FULLTEXT INDEX `name_sdesc_desc_fulltext` (`name`, `short_description`, `description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `products` (`idcategory`, `name`, `short_description`, `description`)
VALUES (1, 'test1', 'sdesc1', 'desc1'), (1, 'test2', 'sdesc2', 'desc2'), (2, 'test3', 'sdesc3', 'desc3');

EXPLAIN SELECT *
FROM products AS p USE INDEX (`name_sdesc_desc`)
WHERE p.idcategory = 1
AND (
    MATCH(p.name, p.short_description, p.description) AGAINST ("*test*" IN BOOLEAN MODE)
);

SELECT *
FROM products AS p USE INDEX (`name_sdesc_desc`)
WHERE p.idcategory = 1
AND (
    MATCH(p.name, p.short_description, p.description) AGAINST ("*test*" IN BOOLEAN MODE)
);
b-frid
  • 116
  • 8
  • Yes, I cant even create the table. I get this: #1170 - BLOB/TEXT column 'name' used in key specification without a key length – Alex Kuzmov Nov 10 '20 at 12:24
  • I had forgot the comma after the `KEY idcategory (idcategory)` line, which I have corrected above. Assuming you hadn't already corrected that yourself when running previously, do you still get the same result if trying again with the corrected syntax above? – b-frid Nov 10 '20 at 14:50
  • See edited answer. Query Set 7 & 8 should provide the way to setup the multi-column index. – b-frid Nov 10 '20 at 20:45
  • Didnt work. It did but, I cant afford the composite index. The actual table I`m trying to use this on has around 500k records and UPDATES are pretty frequent. I tried it in dev env and the selects were more than twice as slow. – Alex Kuzmov Nov 15 '20 at 23:28