Clever Geek Handbook

MySQL subquery is slower than two separate queries

I am puzzling over a simple subquery. I have the following request that runs within 30+ seconds:

SELECT DISTINCT SUBSTRING( 6pp, 1, 4 ) AS postcode
FROM 6pp
WHERE gemeenteID
IN (
    SELECT gebiedID
    FROM tmp_orderimport
    WHERE typeGebied = 'Gemeente'
    AND idorder =1733
    AND STATUS = TRUE );

Explain: Explain

If I break the query in 2 queries and run the IN part first, it all takes less than a second. But of course I prefer to use a single query. The tables are in MYISAM. Any suggestions?

Update:

Following the lead of Gordon Linoff. I changed the SQL to:

SELECT DISTINCT SUBSTRING( 6pp, 1, 4 ) AS postcode
FROM `6pp`
WHERE EXISTS (SELECT 1
          FROM tmp_orderimport oi
          WHERE oi.gebiedID = `6pp`.gemeenteID AND
                typeGebied = 'Gemeente' AND idorder = 1733 AND STATUS = TRUE
         );

And added an index for tmp_orderimport (gebiedID, typeGebied, idorder, status). The request now takes less than 4 seconds to complete.

New explanation: New SQL explain

+3


source to share


4 answers


Try using instead exists

:

SELECT DISTINCT SUBSTRING( 6pp, 1, 4 ) AS postcode
FROM `6pp`
WHERE EXISTS (SELECT 1
              FROM tmp_orderimport oi
              WHERE oi.gebiedID = `6pp`.gemeenteID AND
                    typeGebied = 'Gemeente' AND idorder = 1733 AND STATUS = TRUE
             );


You can also speed it up with an index on tmp_orderimport(gebiedID, typeGebied, idorder, status)

.

MySQL can be inefficient (sometimes depending on the version) when used IN

with a subquery. exists

usually fixes the problem. The particular problem is that the subquery is executed for every comparison. When you create a temporary table, you work around this.

+2


source


Use INNER JOIN instead of IN operator

Try the following:



SELECT DISTINCT SUBSTRING(a.6pp, 1, 4) AS postcode
FROM 6pp a
INNER JOIN tmp_orderimport b ON a.gemeenteID = b.gebiedID 
WHERE b.typeGebied = 'Gemeente' AND b.idorder =1733 AND b.STATUS = TRUE
+1


source


MySQL probably executes the subquery once for each record of the main query. This results in exponential processing.

Its subquery is independent of the main query, you can run the subquery first and store the value in memory (possibly a temporary table) and then use the main query.

0


source


I didn't see any comments about your 6pp table and had an index for that for (gemeenteID, 6pp). I also have an index on your tmp_orderimport table (idorder, tpeGebied, status, gebiedID) and then change it a bit.

SELECT DISTINCT 
      SUBSTRING( P.6pp, 1, 4 ) AS postcode
   FROM 
      ( SELECT gebiedID
          FROM tmp_orderimport
         WHERE typeGebied = 'Gemeente'
           AND idorder =1733
           AND STATUS = TRUE ) G
      JOIN 6pp P
         on G.gebiedID = P.gemeenteID
0


source






More articles:


All Articles