Speed up LEFT JOIN with OR operator in ON clause I have a query that uses a LEFT JOIN with an OR operator within the ON clause. When commenting out the OR operator the query runs in ~150ms. With the OR operator kept in the query takes over 80s to run. Does anyone know how to speed this up? Details below.

supplier_responses
+----+-------------+-------------+----------+-------+
| id | internal_id | supplier_id | supplier | price |
+----+-------------+-------------+----------+-------+
| 1  | 100         | 100         | poggle   | 10    |
| 2  | 101         | 101         | poggle   | 15    |
| 3  | 102         | 290         | lello    | 12    |
| 4  | 103         | 370         | chugs    | 10    |
| .. | ...         | ...         | ...      | ...   |
+----+-------------+-------------+----------+-------+
Indexes on internal_id, supplier_id, supplier.

supplier_updates
+----+-------------+-------------+----------+--------+--------------+
| id | internal_id | supplier_id | supplier | status | timestamp    |
+----+-------------+-------------+----------+--------+--------------+
| 1  | 100         | 100         | poggle   | 80     | 2019-01-15...|
| 2  | 100         | 100         | poggle   | 100    | 2019-01-16...|
| 3  | null        | 290         | lello    | 80     | 2019-01-16...|
| 4  | 107         | 107         | poggle   | 80     | 2019-01-17...|
| 5  | 112         | null        | chugs    | 100    | 2019-01-17...|
| 6  | null        | 100         | lello    | 100    | 2019-01-18...|
| .. | ...         | ...         | ...      | ...    | ...          |
+----+-------------+-------------+----------+--------+--------------+
Indexes on internal_id, supplier_id, supplier, timestamp.


For "poggle" internal_id and supplier_id will be identical.

SELECT * 
FROM   supplier_responses sr 
       LEFT JOIN (SELECT * 
                  FROM   supplier_updates su 
                         LEFT JOIN (SELECT supplier supplier_name, 
                                           COALESCE(internal_id, supplier_id) latest_id, 
                                           Max(timestamp) latest_timestamp 
                                    FROM   supplier_updates 
                                    GROUP  BY supplier_name, 
                                              supplier_id) suLatest 
                                ON suLatest.supplier_name = su.supplier 
                                   AND suLatest.latest_timestamp = su.timestamp 
                                   AND suLatest.latest_id = 
                                       COALESCE(su.internal_id, 
                                       supplier_id) 
                 ) su 
              ON ( sr.supplier = su.supplier 
                   AND sr.internal_id = su.internal_id ) 
                  OR ( sr.supplier = su.supplier 
                       AND sr.supplier_id = su.supplier_id ) 
ORDER  BY su.timestamp_at ASC;
Change LEFT JOIN to JOIN unless you really need LEFT.
Clean up things like ( sr.supplier_id = su.supplier_id AND sr.supplier_id = su.supplier_id )
Change the OR into a UNION (with a lot of rewriting)
Provide SHOW CREATE TABLE (so we can see if you have suitable indexes)
Don't leave out the rest of the conditions -- they may be part of the problem.
Improve the prettyprinting, perhaps


like this:

SELECT  *
    FROM  supplier_responses sr
    LEFT JOIN  
        (
        SELECT  *
            FROM  supplier_updates su
            LEFT JOIN  
                (
                SELECT  supplier_id supplierId,
                        coalesce(internal_id, supplier_id) latest_id,
                        Max(timestamp) latest_timestamp
                    FROM  supplier_updates
                    GROUP BY  supplier_id, supplier_id
                ) suLatest  ON suLatest.supplierId = su.supplier_id
                         AND  suLatest.latest_timestamp = su.timestamp
                         AND  suLatest.latest_id = coalesce(su.internal_id, supplier_id)
        ) su  ON ( sr.supplier_id = su.supplier_id
              AND  sr.internal_id = su.internal_id 
                 )
      OR  (        sr.supplier_id = su.supplier_id
              AND  sr.supplier_id = su.supplier_id 
          ) --
    WHERE  some clause...
    ORDER BY  su.timestamp_at ASC;


Perhaps a composite index like INDEX(supplier_id, internal_id) would be beneficial.