Mysql query performing very slowly

I have a query I’m using in my CodeIgniter model to fetch the count of listings of products between particular days. This works fine when there are less items in my table, but there are more than 100,000 entries in my table and to just get the output of 2 days it takes around 3-4 minutes. The longer the from and to days are apart, the more time it takes.

how to retrieve latest data from mysql table out of duplicate records

I am trying to retrieve latest data from my sql table for each record. There will be duplicate data for each record with some data changes. I need to retrieve the latest timestamped data. Can someone suggest which is the optimum solution in terms of performance. Have seen some solutions with inner joins and sub queries.

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 … Read more