MySQL calculated fields reuse in SELECT clause

Question

How would you accomplish this task to get best performance?

Table schema:

CREATE TABLE `test_truck_report` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `truck_id` INT(11) NOT NULL,
    `odometer_initial` INT(11) NOT NULL,
    `odometer_final` INT(11) NOT NULL,
    `fuel_initial` INT(11) NOT NULL,
    `fuel_final` INT(11) NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

What i'm trying to execute is this query:

SELECT
    truck_id,
    (odometer_final - odometer_initial) AS mileage,
    (fuel_initial - fuel_final) AS consumed_fuel,
    (consumed_fuel / mileage) AS consumption_per_km
FROM
    test_truck_report
WHERE
    consumption_per_km > 2

Somehow this obvious on the first sight logic doesn't work and i'm forced to use this query instead:

SELECT
    truck_id,
    (odometer_final - odometer_initial) AS mileage,
    (fuel_initial - fuel_final) AS consumed_fuel,
    ((fuel_initial - fuel_final) / (odometer_final - odometer_initial)) AS consumption_per_km
FROM
    test_truck_report
WHERE
    ((fuel_initial - fuel_final) / (odometer_final - odometer_initial)) > 2

I assume that constant recalculation of each calculated field every time where it needs to be placed makes significant performance downgrade. And this is just a test case, actual working table has 50+ fields and some of calculated fields consists of 10+ operands. So it's a really HUGE problem at the moment.

Reason why i don't want to actually create these fields and perform something like:

UPDATE 
    `test_truck_report` 
SET
    consumed_fuel = fuel_initial - fuel_final

is that existing records are being constantly updated by the users and in that case i would need to constantly update that data. So do you consider creating actual fields a better idea? Or is there some better way?

Thanks.

Take a look at the [`HAVING`](http://dev.mysql.com/doc/refman/5.7/en/select.html) clause for `SELECT` statements. This one uses column names and "calculated values". — Linus Kleen, Jan 19 '15 at 11:37

score 1 · Accepted Answer · edited May 23 '17 at 11:50

Try to use views:

We need an auxiliary view:

CREATE OR REPLACE VIEW vw_truck_data AS
SELECT truck_id,
      (odometer_final - odometer_initial) AS mileage,
      (fuel_initial - fuel_final)         AS consumed_fuel
FROM test_truck_report;

And the final view:

CREATE OR REPLACE VIEW vw_truck_consumption AS
SELECT data.*,
      (data.consumed_fuel / data.mileage) AS consumption_per_km
FROM vw_truck_data data;

Now you can query whenever you want in an easy and readable way:

SELECT *
  FROM vw_truck_consumption 
 WHERE consumption_per_km > 2

This way MySQL should be able to only substract each field once so the performance should be at least as good as your solution or better. Normally the CPU cost from adding fields is smaller than the cost to retreive data from the database but of course it depends on your hardware, mysql version, configuration and data distribution. Do some measurements if it is really an issue.

Anyway remmember that you are making a query filtering by consumption_per_km which is a funtion of fields. As MySQL seems to lack funtional indexes it will surely scan the full table and be slow.

MySQL calculated fields reuse in SELECT clause

1 Answers1