Index Column Nullable MySql

I have a column of type DateTime being nullable, this same column is indexed. But when execute the Select, the index not is used.

CREATE TABLE `te3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dataHora` datetime DEFAULT NULL,
`nome` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IDX_DATAHORA` (`dataHora`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;



SELECT *
FROM te3 
where dataHora between '2018-10-11 15:18:16' AND '2018-10-11 15:18:50';

Extra Using where

Not use index.

The query is slow, what should I do? Just remembering that the column has to be nullable

1 answer

  • answered 2018-10-11 19:13 Lukasz Szozda

    Your index could be set as INVISIBLE:

    MySQL supports invisible indexes; that is, indexes that are not used by the optimizer. The feature applies to indexes other than primary keys (either explicit or implicit).

    ALTER TABLE te3 ALTER INDEX index_name VISIBLE;
    

    Checking:

    SELECT INDEX_NAME, IS_VISIBLE
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_NAME = 'te3';