Home SQL left join takes too long
Reply: 2

SQL left join takes too long

TobSta
1#
TobSta Published in 2018-02-14 19:52:48Z

I need to run a sql query that takes 24s. I tried to create indices on the two datetime columns START_DATE and END_DATE but my connection gets interupted after 600s. Is there any way to write a faster query?

SELECT tbl1.*,
         tbl2.NAME
         FROM (  SELECT * FROM table1
                WHERE LOC_ID IN (%s)
                AND START_DATE != END_DATE
                AND START_DATE <= '2002-01-31' 
                AND END_DATE >= '2002-01-01') tbl1
         LEFT JOIN 
            table2 as tbl2
                ON tbl1.ID = tbl2.ID

EDIT: I tried moving the where clause outside which increased the duration to 120 seconds.

I changed the query to the suggestion but it still takes the 24s, i gained a few ms only

JNevill
2#
JNevill Reply to 2018-02-14 21:31:09Z

I believe the following would be synonymous to your current logic and wouldn't destroy the ability to use the index on your dates:

SELECT tbl1.*,
     tbl2.NAME
FROM table1 tbl1
     LEFT JOIN table2 as tbl2
        ON tbl1.ID = tbl2.ID
WHERE tbl1.LOC_ID IN (%s)
     AND tbl1.START_DATE <> tbl1.END_DATE
     AND tbl1.START_DATE <= '2002-01-31' 
     AND tbl1.END_DATE >= '2002-01-01';

Even without an index, I suspect this, or the subquery form of this, would be quicker since the CPU doesn't have to split the dateparts for your two fields on EVERY record in the table to perform the comparison.

Rick James
3#
Rick James Reply to 2018-02-22 13:09:10Z
SELECT  *, 
        ( SELECT  NAME
            FROM  table2
            WHERE  ID = table1.id 
        ) AS NAME
    FROM  table1
    WHERE  LOC_ID IN (%s)
      AND  START_DATE != END_DATE
      AND  START_DATE <= '2002-01-31'
      AND  END_DATE   >= '2002-01-01';

With these indexes:

INDEX(START_DATE),
INDEX(END_DATE),
INDEX(LOC_ID, START_DATE),
INDEX(LOC_ID, END_DATE)

The date range you have is essentially impossible to optimize; having multiple indexes gives the Optimizer a choice; it will pick from them based on the distribution of the data. That is, changing the month in question may lead to using a different index.

The indexes starting with LOC_ID are an optimization for (1) a single LOC being used, or (2) LOC does a better job of filtering than either date.

The subquery will fail if the relationship is 1:many; I am guessing that it is 1:1. Why have table2? Usually (not always) it is unwise to have two tables in a 1:1 relationship.

Don't use SELECT * unless you really need all the columns.

Please provide EXPLAIN SELECT ..., SHOW CREATE TABLE, and how many rows are in the table.

What version of MySQL? (Newer versions will perform this query better.)

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.299548 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO