1

So I have a query:

(ROW_NUMBER() OVER (PARTITION BY itm.storeID, itm.skuID ORDER BY itm.effectiveDate DESC)) AS rn,

Then what I would do is use foreach in the the table then scan if rn is equal to 1, if equal then I will add to a value, so basically I sum all the rows whose rn is equal to 1...

The problem is the query for the row_number is slow (not the foreach) since it's returning 400,000+ of rows. The query is per month so I get a total of 30 rn per each partition. Then I only need the row whose rn is equal to 1...

Is there a way to do this faster? If I just wrap the query like this:

SELECT * FROM (... rn query) t WHERE t.rn = 1

then I am still fetching the entire rn then only will it filter, so I'm not really making the query faster...

I also tried to put it in temp table since I am using Laravel but it doesn't work since I am using different DB_HOST for read and write...

Rick James
  • 135,179
  • 13
  • 127
  • 222
jove0610
  • 664
  • 1
  • 8
  • 16
  • Do not use window function, try to use aggregating subquery. Maybe it will be more effective in your particular case... – Akina Nov 11 '21 at 08:56
  • In the duplicate question there are at least 6 different methods described how to get the record with max value per group. Try the various options and see which one works the best for you. – Shadow Nov 11 '21 at 09:19
  • MySQL has always had a habit of delivering a feature, but not optimizing it for a couple of major releases. So, I don't hold out much hope for `OVER ( PARTITION ... )` in the near future. – Rick James Nov 11 '21 at 23:56

1 Answers1

1

One approach might be fetching the MAX value of effectiveDate for a partition, in a separate Subquery (Derived Table). Afterwards, you can JOIN back to the main table, to get that specific row.

Defining a proper Composite index here will also help.

Try the following:

SELECT itm.* FROM itm 
JOIN (SELECT store_id, skuID, MAX(effectiveDate) AS maxDate 
      FROM itm 
      GROUP BY storeID, skuID) AS dt 
  ON dt.skuID = itm.skuID
 AND dt.storeID = itm.storeID 
 AND dt.maxDate = itm.effectiveDate

You will need to define a Composite Index: (storeID, skuID, effectiveDate) to get the best performance.

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57