CODEWITHSUNDEEP
mysqljoinquery-optimizationleft-join
James Glass
James Glass

Reputation: 4300

MySQL Left Join running very slow, splitting into 2 queries is much faster

When I run my query with a LEFT JOIN, it takes 20 seconds. When I split it into 2 queries, it runs in 4 seconds. However, as you can imagine, this makes it very difficult to display the results in a certain order. What is wrong with my initial query, and can I combine the 2 into 1?

Table1 has ~400,000 rows and Table2 has ~200,000 rows.

Field3 is the primary key in Table2, and is indexed in Table1.

The queries return approx ~100k rows. 

SELECT Table1.Field1, Table2.Field4
FROM
    Table1
    LEFT JOIN
        Table2 USING (Field3)
WHERE Table1.Field1 = 'Example'

When I run the query as written, it takes on average 20 seconds to run. But if I run the query without the LEFT JOIN, and run a 2nd query to get that data, everything runs in total in about 4 seconds.

SELECT Table1.Field1, Table1.Field3
FROM Table1
WHERE Table1.Field1 = 'Example'

//PHP
$first = true;
while ($row = mysql_fetch_assoc($query))
{
    if (!$first)
    {
        $field3Values .= ",";   
    }
    $field3Values .= "'" . mysql_real_escape_string($row['Field3']) . "'";  
    $first = false;
}

//Now run the 2nd query
SELECT Field4
FROM People
WHERE Field3 IN (" . $field3Values . ")

Edit: As requested, the EXPLAIN from the first query: enter image description here

Upvotes: 0

Views: 567

Answers (1)

G-Nugget
G-Nugget

Reputation: 8836

John and I figured this out in a chat. The single query was slow because the query optimizer wasn't selecting a good index for Table1. By adding FORCE INDEX (Field1) to the query, the index on Table1.Field1 was used to filter the results instead of doing a full table scan. We're not completely sure why the two query approach was running faster, but my guess is that for some reason the query optimizer was using the index in the first of the single queries, but not in the single.

Upvotes: 1

Related Questions