Home / Questions / 21290
Accepted
El Cóndor
Asked: 2020-08-23 07:07:59 +0800 CST 2020-08-23 07:07:59 +0800 CST

When are indexes not used despite being declared in a MySQL table?

  • 772

I mean what are the possible cases where a MySQL query does not occupy the indexes created on a table. I ask because I perform a query where I get 39000 records out of 41000 existing ones, using the statement explainI can notice that the indexes of the table are not being used. So can it be because it only fetches records from a single table and it's almost all of them? and if so, in what other cases could the same thing happen (indices not being used)?

mysql

1 Answers

  1. Best Answer

    MySQL uses the indexes it deems convenient to optimize the results based on the columns found in the section WHERE, regardless of the order in which they are found. Let's see the example using the following DDL:

    CREATE TABLE tabla (
    c1 INT PRIMARY KEY AUTO_INCREMENT,
    c2 VARCHAR(50),
    c3 DATE NOT NULL,
    c4 VARCHAR(15) NOT NULL,
    INDEX prueba (c3, c4)
);

    And with the following set of test data:

    INSERT INTO tabla VALUES
(NULL, 'Casilla 1', CURDATE(), 'OPEN'),
(NULL, 'Casilla 2', CURDATE(), 'OPEN'),
(NULL, 'Casilla silenciosa', DATE_ADD(CURDATE(), INTERVAL 1 DAY), 'CLOSED'),
(NULL, 'Casilla cuadrada', DATE_ADD(CURDATE(), INTERVAL 1 DAY), 'CLOSED'),
(NULL, 'Casilla no cuadrada', DATE_ADD(CURDATE(), INTERVAL 1 DAY), 'OPEN'),
(NULL, 'Casilla que no debe cerrarse', DATE_ADD(CURDATE(), INTERVAL 2 DAY), 'CLOSED'),
(NULL, 'Casilla 3', DATE_ADD(CURDATE(), INTERVAL 2 DAY), 'CLOSED'),
(NULL, 'Casilla de pensamientos', DATE_ADD(CURDATE(), INTERVAL 3 DAY), 'OPEN');

    When executing the EXPLAINon the queries we obtain interesting results:

    -- usa el índice
EXPLAIN
SELECT c1, c2
FROM tabla
WHERE c3 = CURDATE() AND c4 IN ('OPEN', 'CLOSED');

-- usa el índice
-- el orden de las columnas no es problema para MySQL
EXPLAIN
SELECT c1, c2
FROM tabla
WHERE c4 IN ('OPEN', 'CLOSED') AND c3 = CURDATE();

-- usa el índice
-- a pesar que en el WHERE primero se encuentre c2
-- que no es parte del índice
EXPLAIN
SELECT c1, c2
FROM tabla
WHERE c2 LIKE '%silenciosa%'
 AND c3 = CURDATE() AND c4 IN ('OPEN', 'CLOSED');

-- usa el índice
-- a pesar que en el WHERE se encuentre c2 entre c3 y c4
EXPLAIN
SELECT c1, c2
FROM tabla
WHERE c3 = CURDATE()
 AND c2 LIKE '%silenciosa%'
 AND c4 IN ('OPEN', 'CLOSED');

    It should be noted that you can force the use of a particular index by means of the statement FORCE INDEX, as will be seen in the following cases:

    -- no usará el índice porque MySQL detecta
-- que al usar >= para c3 es igual que
-- realizar un full scan a la tabla
EXPLAIN
SELECT c1, c2
FROM tabla
WHERE c3 >= CURDATE() AND c4 IN ('OPEN', 'CLOSED');

-- usa el índice
-- se fuerza al motor a utilizar el índice con la sentencia
-- FORCE INDEX (<nombre del índice a utilizar>)
EXPLAIN
SELECT c1, c2
FROM tabla
FORCE INDEX (prueba)
WHERE c3 >= CURDATE() AND c4 IN ('OPEN', 'CLOSED');
    • 2