CODEWITHSUNDEEP
mysql
Sari Rahal
Sari Rahal

Reputation: 1955

SQL Left Join taking 12 min

I have 2 tables that I am trying to compare. The tables are the same structure and should be exactly the same with 60,000 entries each. However some entries are different and I would like to find the issues. I am currently trying to do a left join on each of them and show the results. I am currently getting results but it takes 12 min for each Query.

Table 1 (temp_entries)

ID | File

Table 2 (temp_dir_scan)

ID | File

Query 1 (12.25 min)

SELECT A.ID, A.File 
  FROM temp_entries A 
  LEFT JOIN temp_dir_scan B ON A.File = B.File 
 WHERE B.File is Null

Query 2 (12.26 min)

SELECT A.File 
  FROM temp_dir_scan A 
  LEFT JOIN temp_entries B ON A.File = B.File 
 WHERE B.File is Null

Query 3 (11.54 Min)

SELECT A.ID, A.File 
  FROM temp_entries A 
 Where A.File not in (select B.File from temp_dir_scan B)

What is the matter? Or what can I do to speed this up? What is a reasonable time to complete this in?

Here are examples of the results.

1|test.txt
2|test2.txt
45|temp.jpg

Upvotes: 0

Views: 74

Answers (1)

vmachan
vmachan

Reputation: 1682

As mentioned in the comment, you could try and do a FULL OUTER JOIN, an example using your tables shown below (MySQL does not have FULL joins so using UNION to emulate this)

UPDATED: To remove duplicate column errors

SELECT C.A_ID
     , C.A_File 
     , C.B_ID
     , C.B_File
  FROM (
    SELECT A.ID AS A_ID
          ,A.File AS A_File
          ,B.ID AS B_ID
          ,B.File AS B_File
      FROM temp_entries A
      LEFT 
      JOIN temp_dir_scan B
        ON A.File = B.File
     UNION
    SELECT A.ID AS A_ID
          ,A.File AS A_File
          ,B.ID AS B_ID
          ,B.File AS B_File
      FROM temp_entries A
     RIGHT 
      JOIN temp_dir_scan B
        ON A.File = B.File
       ) C
WHERE C.A_File IS NULL 
   OR C.B_File IS NULL

This SO post gives more information about doing FULL OUTER JOINS in MySQL.

Hope this helps

NOTE: Unsure how much of a performance gain is seen compared to your original query(ies).

Upvotes: 1

Related Questions