1

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
  • The optimizer may not see the subquery correlation. Try replacing WHERE t2.confirmada = 'S' with WHERE t2.confirmada = t.confirmada. – mustaccio Jun 29 '15 at 20:47
  • 'confirmada' is not a key, is a Yes/No value. in this example it maches the primary query 'confirmada' field, but it also applies if I have confirmada = 'N' in the dependant query. In both cases, is using the idxConfirmada. Therefore, this change is not solving it. Any other suggestion ? – nerlijma Jun 29 '15 at 21:02
  • You also don't need LEFT JOIN Transacciones t2 ON rtr2.idTransaccion = t2.id -- an INNER JOIN will have the same result. – mustaccio Jun 29 '15 at 21:24
  • You may not have noticed, based on the boxes you drew, but the optimizer has actually swapped the order of the last 2 tables in the query plan. – Michael - sqlbot Jun 29 '15 at 21:50
  • As a last possibility, you can "disable" efficient usage of index for any column by comparing not its value but return of some function. For numerical columns simple "col+0 = X" is not going to use index on col in most cases, for string coalesce, or concat should be enough: "concat(t2.confirmada, '') = 'S'" - thats a good thing for testing but try to resolve it some other way (composite index mentioned in RJ's answer sounds really good) – jkavalik Jun 30 '15 at 6:10
3

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.

  • added table details as suggested. I just want the ORM to choose the correct index. Is not a matter of statistics ? Chosing the correct index 'primary with idTransaccion' (second explain plain) would result in only subquering for the given 10 records and matching by idTransaccion. The first explain plan, would result in scaning the whole table (as most of the transactions are confirmada = 'S') for each of the 10 rows in the primary query. Again, statistics are not having any participation here? – nerlijma Jun 30 '15 at 12:45
  • I made some edits. – Rick James Jun 30 '15 at 14:14
  • @RickJames the column is id, not idTransaction. – ypercubeᵀᴹ Jun 30 '15 at 14:23
  • Oops, that makes most of what I said irrelevant. I'll fix it in a minute. – Rick James Jun 30 '15 at 14:38
  • See if that makes more sense. – Rick James Jun 30 '15 at 14:46

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.