[SOLVED] Is it possible to get only rn = 1 in ROW_NUMBER without getting the rest of the rn to increase query speed?

Is it possible to get only rn = 1 in ROW_NUMBER without getting the rest of the rn to increase query speed?

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...

Solution

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.