0

I have a simple query on a table with 66k rows and the index is not used.

Query :

select * from products p where p.upc = '771313251785'

MySQL version : 8

Here is the create table :

CREATE TABLE `produits`
             (
                          \n `pharmacieidbjc` INT NOT NULL,
                          \n `upc`            VARCHAR(14) DEFAULT NULL,
                          \n `sap`            VARCHAR(15) DEFAULT NULL,
                          \n `iditem`         VARCHAR(20) NOT NULL,
                          \n `description`    VARCHAR(100) DEFAULT NULL,
                          \n `fournisseur`    VARCHAR(10) DEFAULT NULL,
                          \n `coutant` DOUBLE DEFAULT NULL,
                          \n `prix` DOUBLE DEFAULT NULL,
                          \n `categorie`       VARCHAR(10) DEFAULT NULL,
                          \n `sousdepartement` VARCHAR(10) DEFAULT NULL,
                          \n `departement`     VARCHAR(10) DEFAULT NULL,
                          \n `marque`          VARCHAR(30) DEFAULT NULL,
                          \n `din`             VARCHAR(8) DEFAULT NULL,
                          \n `crx`             VARCHAR(6) DEFAULT NULL,
                          \n `esttaxablefed`   TINYINT(1) DEFAULT NULL,
                          \n `esttaxableprov`  TINYINT(1) DEFAULT NULL,
                          \n PRIMARY KEY (`pharmacieidbjc`,`iditem`),
                          \n KEY `ix_upc` (`upc`)
                          /*!80000 INVISIBLE */
                          ,
                          \n KEY `ix_sap` (`sap`),
                          \n KEY `ix_din` (`din`),
                          \n KEY `ix_dep` (`departement`),
                          \n KEY `ix_sousdep` (`sousdepartement`),
                          \n KEY `ix_cat` (`categorie`),
                          \n FULLTEXT KEY `ix_upc_full` (`upc`)\n
             )
             engine=innodb DEFAULT charset=utf8

Index configuration Plan

0
3

The output of SHOW CREATE TABLE explains your issue. The index was declared invisible. Read about them at: MySQL 8.0 docs: Invisible Indexes where it is explained:

MySQL supports invisible indexes; that is, indexes that are not used by the optimizer. ...

ALTER TABLE produits ALTER INDEX ix_upc VISIBLE ;

should make the index visible again and be used by the optimizer.

While not disallowed, it is usually inappropriate to have both FULLTEXT and INDEX indexes on the same column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.