How to optimize a SQL query with over 2 million rows

Question

I have a Sql database with over 2 million rows and it is growing rapidly. There aren't many columns, just code, price, date, and stationID. The purpose is to get the latest price by code and stationID. The query works great but takes over 10 seconds. Is there any way to optimize the query? $statement=$this->pdo->prepare('WITHcteAS(SELECTstationIDASind,code,CAST(priceASDOUBLE)ASp

P粉141455512 · Answer

I guess you need the following indexes for the query to execute well (you only need to do it once as part of the database design).

CREATE INDEX IX ON price
  (code, stationID, date DESC, price)

The first two columns can be arranged in any order.

P粉297434909 · Answer

As long as there can't be two rows of the same code, stationID pair with the same datetime, using window functions is a bit like using a sledgehammer to crack a nut.

select p.stationID, p.code, p.price, p.date
from (
    select code, stationID, max(date) as max_date
    from price
    group by code, stationID
) max
join price p
    on max.code = p.code
   and max.stationID = p.stationID
   and max.max_date = p.date;

It requires the following indexes:

alter table price add index (code, stationID, date desc);

This query should take less than 1 millisecond because the derived table can be built from the index and then it reads only the required rows from the table.

Alternatively, you can significantly reduce the effort if you know that each code, stationID pair will receive an updated price within a specific time period (1 hour, 1 day, 1 week) The window function needs to add a where clause:

with cte as 
(
    select stationID as ind, code, price, date, row_number() over(partition by code, stationID order by date desc) as latest
    from price
    where date >= now() - interval 1 week
)
select * from cte where latest  = 1;