Question 183249 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to optimize this sql query on + 2 million rows

I have a Sql database with +2 million rows and growing fast. There aren’t many columns, only code, price, date and stationID.

The aim is to get the latest price by code and stationID.
The query works well, but takes more than 10s.

Is there a way to optimize the query?

 $statement = $this->pdo->prepare(

        'WITH cte AS 
        (

        SELECT stationID AS ind, code, CAST(price AS DOUBLE ) AS price, date

        ,ROW_NUMBER() OVER(
             PARTITION BY code, stationID
             ORDER BY date DESC
         ) AS latest

        FROM price
        )
        
        SELECT *
        FROM cte
        WHERE latest  = 1
        '
    );

    $statement->execute();
    $results = $statement->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);

Edit:
There is an index on the first column called ‘id’. I don’t know if this helps.

The database (InnoDB) look like this:

id primary - int
stationID - int
code - int
price - decimal(10,5)
date - datetime
Tags:

2

Answers


  1. 0

    I would imagine you need the following index for your query to perform well (you only need to do this once, as part of your database design).

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

    The first two columns can be in either order.

    Login or Signup to reply.
  2. 0

    I think that you are selecting and then partitioning all the rows from db table, and when it is few millions of rows, just selecting them may be slow. I would consider pagination of selects – that means you change your application more generally. I can’t imagine why you select all rows from large table? Why you don’t filter it. Maybe you can also try to replace partition with LEFT JOIN and GROUP BY, to compare speeds

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search