The best thing you can do is drop the
id column as it:
- Allows absolute junk to be inserted into your table
- Prevents utilizing MySQL's clustered index to optimize the query
- Takes up space, requiring more page reads to return the same amount of information
The primary key is
(Meter,Datetime)1, so we should define it as such. This will:
- Prevent inconsistent data (we should not have two values for the same
Meter for the same
- Store the rows (more or less) according to the order of the primary key. This means fewer physical page reads, and a simple way to get the prior value without sorting the data first.
Depending on your version of MySQL, you can fulfill your request one of two ways: a join to retrieve the prior value, or a windowing function.
Using a join (works in all versions):
,PriorMM.Record_A AS Prior_Record_A
,MM.Record_A - PriorMM.Record_A AS Record_A_Change
ON PriorMM.Meter = MM.Meter
AND PriorMM.Datetime =
Meter = MM.Meter
AND Datetime < MM.Datetime
Using a windowing function (supported with MySQL 8.0):
,LAG(Record_A) OVER (PARTITION BY Meter ORDER BY Datetime) AS Prior_Record_A
,Record_A - LAG(Record_A) OVER (PARTITION BY Meter ORDER BY Datetime) AS Record_A_Change
Which one of these will perform better depends entirely on if MySQL understands the clustered index eliminates a need for a sort when the windowing function is used.
1 I would recommend using a column name that isn't a reserved keyword in many databases - consider