Thesql query choosing an incorrect execution plan

execution-planexplainindex-tuningMySQL

I have a primary with an dependant subquery as a where condition that is choosing a wrong execution plan, and therefore is running for ever.

Main query:

SELECT  rtr.id AS id156_,
           rtr.procesada AS procesada156_,
           rtr.idTransaccion AS idTransa3_156_
FROM
   ReservasTiempoReal rtr 
        INNER JOIN Transacciones t ON t.id=rtr.idTransaccion
        INNER JOIN TransaccionLineas tl ON tl.idTransaccion = rtr.idTransaccion
        INNER JOIN Productos prod ON prod.id=tl.idProducto
        INNER JOIN Proveedores prov ON prov.id=prod.idProveedor
WHERE t.confirmada = 'S'
    AND t.codTipoTransaccion = 'RTRCT'
    AND t.fechaHora >= '2015-01-20 15:30:59'
    AND t.fechaHora <= '2015-01-21 15:30:59'
    AND prov.id=77        
       AND (EXISTS ( SELECT rtr2.id
                       FROM ReservasTiempoReal rtr2
                       INNER JOIN Transacciones t2 ON rtr2.idTransaccion = t2.id
                       WHERE t2.confirmada = 'S'
                                AND t2.codTipoTransaccion = 'ECTR'
                                AND t2.idTransaccionOriginal = rtr.idTransaccion));

And its execution plan:

enter image description here

In red is shown the used index using ‘codTipoTransaccion’ and ‘confirmada’ columns.
The first query (without the inner one) returns only 10 rows. But the whole transaction table has over 5 millon records. And most of them have the attribute confirmada = ‘S’.
If I delete the index ‘idxConfirmada’ (index by ‘confirmada’ field) the query takes only 10 seconds to execute and the execution plan is:

enter image description here

Just to add more info, if I execute the dependent inner query only with the records identified in the primary query, it returns only 6 records. The used query was:

SELECT rtr2.id
FROM ReservasTiempoReal rtr2
      INNER JOIN Transacciones t2 ON rtr2.idTransaccion = t2.id
WHERE t2.confirmada = 'S'
      AND t2.codTipoTransaccion = 'ECTR'
      AND t2.idTransaccionOriginal IN (14748319,
14748519,14750327,14751060,14751126,14751351,14751354,14752902,14754752,
14754785)

I don’t want to delete the ‘idxConfirmada’ index, I need the ORM to choose the correct plan. Also, I cannot use hints to ignore the index as the query is coming from hibernate.

How can I make mysql to choose the correct plan (not using idxConfirmada) ?

I am using MYSQL 5.1.51.

Added table details

CREATE TABLE `Transacciones` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `fechaHora` datetime NOT NULL ,
   `nroTicket` varchar(255) NOT NULL ,
   `puntosGenerados` decimal(19,2) DEFAULT NULL,
   `creditoPendiente` decimal(19,2) DEFAULT NULL,
   `debitoPendiente` decimal(19,2) DEFAULT NULL,
   `saldoReal` decimal(19,2) DEFAULT NULL,
   `idTransaccionOriginal` bigint(20) DEFAULT NULL,
   `codTipoTransaccion` varchar(5) DEFAULT NULL,
   `confirmada` varchar(1) NOT NULL,
   `nroLote` varchar(5) DEFAULT NULL,
   `idMonedaImporteFinal` bigint(20) DEFAULT NULL,
   `totalImporteFinal` decimal(19,2) DEFAULT NULL,
   `anulada` varchar(1) DEFAULT NULL,
   `idLote` bigint(20) DEFAULT NULL,
   `online` varchar(1) DEFAULT NULL,
   `manualConPIN` varchar(1) DEFAULT NULL,
   `manualSinPIN` varchar(1) DEFAULT NULL,
   `facturada` varchar(1) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `FK2D546D5D771F9D3A` (`idTransaccionOriginal`),
   KEY `idxAnulada` (`anulada`),
   KEY `idxConfirmada` (`confirmada`),
   KEY `idxCodTipoTransaccion` (`codTipoTransaccion`),
   KEY `idxFechaHora_CodTipoTransaccion` (`fechaHora`,`codTipoTransaccion`),
   KEY `idxNroTicket` (`nroTicket`),
   KEY `idxNroLote` (`nroLote`),
   KEY `idxFechaHora` (`fechaHora`),
   KEY `idxOnline` (`online`),
   KEY `idxFacturada` (`facturada`),
   CONSTRAINT `FK2D546D5D771F9D3A` FOREIGN KEY (`idTransaccionOriginal`) REFERENCES `Transacciones` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=15359563 DEFAULT CHARSET=latin1


CREATE TABLE `ReservasTiempoReal` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `procesada` varchar(1) NOT NULL,
   `idTransaccion` bigint(20) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `idTransaccion` (`idTransaccion`),
   KEY `FK2375B195C078E769` (`idTransaccion`),
   KEY `idxProcesada` (`procesada`),
   CONSTRAINT `FK2375B195C078E769` FOREIGN KEY (`idTransaccion`) REFERENCES `Transacciones` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=50805 DEFAULT CHARSET=latin1

Best Answer

Use a composite index:

INDEX(confirmada, codTipoTransaccion, idTransaccionOriginal, idTransaccion)

The columns can be in any order, so I recommend shuffling them to meet other need(s) and/or facilitate removing some other index from t2.

If you need to discuss this further, please provide SHOW CREATE TABLE for each table.

Edit

For

JOIN ...    ON rtr2.idTransaccion = t2.id
WHERE t2.confirmada = 'S'
  AND t2.codTipoTransaccion = 'ECTR'
  AND t2.idTransaccionOriginal IN (14748319, 14748519, ...)

it is probably better to have this:

INDEX(codTipoTransaccion, confirmada, idTransaccionOriginal)

with idTransaccionOriginal deliberately last (because of the IN).

That should encourage the optimizer to start with t2. I see that idTransaccion is an index in the other table, so the JOIN should be efficient. (On a cold cache, there could still be a lot of I/O.)

Your CREATE TABLE multiple not-so-good indexes for the optimizer (not the ORM) to choose from. What I am proposing should be a 'better' index that the optimizer will readily pick. The optimizer looks at all the indexes, ignores any that are obviously useless (for the given query), and looks at statistics. In some situations (perhaps not yours), the statistics are poor, leading to a poor choice of INDEX. The "statistics" for one index is one number; it assumes a balanced distribution (which you point out is wrong for confirmada='S').

Using a composite index (when it can be used) is almost always better than having shorter indexes.

(Unrelated) DROP the second of these, as being redundant:

 KEY `idxFechaHora_CodTipoTransaccion` (`fechaHora`,`codTipoTransaccion`),
 KEY `idxFechaHora`                    (`fechaHora`),

Also, my suggested index would make KEY idxCodTipoTransaccion (codTipoTransaccion) unnecessary.

More discussion on indexing.

Related Question