Optimizing Sub Query Getting Last Entry Based On Group

Question

We had a bug in our code that cached the wrong values to the last_order_id (expected_previous_order) column. The query I wrote properly finds the correct last order id but is too slow for our data set.

I want to select this data into another table but I cannot because the query will take too long

I have setup a simple example here with a smaller data set. Original table has about 170k rows.

SQL Fiddle of my Example

In the example:

• original_artwork_id is how these rows are grouped.
• order_id is the current rows order id
• actual_previous_order is the corrected last order id
• expected_previous_order is the currently stored last order id. This is the wrong value as it does not actually reference the last order id

EXPLAIN Results

EDIT

Every time a reorder is placed a new entry is placed into the order_artwork table with a reference to the original_artwork_id and last_order_id.

The reference in the current data set to the last_order_id is wrong.

I need to update all records to properly indicate the last order id.

I am doing this by trying to find each artwork and joining it with the previous entry of the same original_artwork_id. Then I can pull the order_id from the last entry to update the current entries last_order_id

Join the current row with the previous row created before the current row with the same original_artwork_id or the current row original_artwork_id = the previous rows id

Answer 1

Not sure if this will be faster than your current query. But anyway.

SQL DEMO

First you need add a new field

`pos` int DEFAULT 0,

And update your base case so can do the JOIN.

update `order_artwork` o
SET `original_artwork_id` = `id`
WHERE  `original_artwork_id` IS NULL;

You could use COALESCE(original_artwork_id, id) but cant use index on that case.

Then assign a row_number to each order based in original_artwork_id and date

update `order_artwork` o
left join (
  SELECT o.id,
         @rn := if(@order_id = `original_artwork_id`,
                   @rn + 1,
                   if(@order_id := `original_artwork_id`, 1, 1)
                   ) as rn
  FROM `order_artwork` o
  CROSS JOIN (SELECT @id := 0, @order_id := 0, @rn := 0) as var
  ORDER BY `original_artwork_id`,
           `created`
) b on
    o.id = b.id
set
    o.pos = b.rn;

Finally update the last order.

UPDATE `order_artwork` o
JOIN (
      SELECT o1.original_artwork_id,
             o2.order_id,
             o1.order_id as last_order_id
      FROM `order_artwork` o1
      LEFT JOIN `order_artwork` o2
        ON o1.pos = o2.pos - 1
       AND o1.original_artwork_id  = o2.`original_artwork_id`
      WHERE o2.pos IS NOT NULL 
      ) as b
 ON o.original_artwork_id = b.original_artwork_id
AND o.order_id = b.order_id
SET o.last_order_id = b.last_order_id;

Answer 2

I found that the created time column was not reliable. So I decided to just find the last highest order id with the same original_artwork_id.

Create a table that has the corrected values

CREATE TABLE order_artwork_two AS 
   select 
      d1.id, 
      d1.order_id, 
      max(d2.order_id) last_order_id, 
      d1.original_artwork_id 
   from order_artwork d1
   left join order_artwork d2 
       ON d1.original_artwork_id = d2.original_artwork_id 
           and d1.order_id > d2.order_id 
   group by d1.original_artwork_id, d1.order_id;

Add an index to the new table. Otherwise the update would be way too slow

alter table order_artwork_two add primary KEY(id);

Update our original table.

update order_artwork d1 
    left join order_artwork_two d2 on d2.id = d1.id
    set d1.last_order_id = d2.last_order_id;