-1

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;
  • If supplier_id and supplier are 1:1, then that pair should not show up in multiple tables. Please redo the schema, then the query, then we can continue with a fresh Q&A. – Rick James Jan 16 at 19:09
  • Apologies Rick, in changing the headings of the tables/columns to simplify it for SE, I'd messed up the query. I've just edited the query. Hopefully it should now be clear supplier_id and supplier are not the same. – Robert Samarji Jan 16 at 21:23
  • But if supplier_id = 100 always refers to supplier = 'poggle', then that mapping should be in only one table. – Rick James Jan 16 at 21:33
  • supplier_id = 100 won't always refer to supplier = 'poggle'. You could also have a supplier_id = 100 referring to another another supplier. Just updated the question to provide an example. – Robert Samarji Jan 16 at 21:36
  • Sorry, the convention of xx_id and xx (or xx_name) confused me. – Rick James Jan 16 at 22:19
0
  • 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.

  • @Kondybas - yeah,tired... – Vérace Jan 16 at 14:58
  • @Rick James, thanks for your answer. I've indexed the appropriate columns and I've also tested with the conditions. Going through block by block, it's just the OR operator that's slowing it. I don't suppose you have any tips on rewriting with union and/or with a composite index? I've tried but with no luck! – Robert Samarji Jan 16 at 16:23
  • @Rick James, noted on your 2nd point. It was a mistake in rewriting it on here, just edited the query to what it should be. – Robert Samarji Jan 16 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.