CODEWITHSUNDEEP
mysqlsql
mtmacdonald
mtmacdonald

Reputation: 15070

MySQL efficiency - do repeated statements in one query get executed multiple times?

In a query such as this:

SELECT 
AVG(var1) AS var1_average,
AVG(var2) AS var2_average,
(
 AVG(var1) + 
 AVG(var2)
)/2.0 AS total_average
FROM readings

do statements like AVG(var1) that appear multiple times get executed multiple times, or are they more efficiently cached internally?

My question comes from trying to understand performance / efficiency. Is it better to write this as some form of subquery? How big a difference will it make?

Upvotes: 2

Views: 255

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is a very arcane area of optimization, the optimizing of common sub expressions. In your example, it makes little difference. The group by sorts the data, which is much more expensive than doing the average.

There are other calculations, such as count(distinct), that are more expensive. In that case, your question becomes more obvious.

Stephen's solution of comparing to a subquery probably works well enough in this case. However, the subquery incurs a lot of overhead itself, because it creates a temporary table. So, you are not comparing apples and apples.

If you really want to compare the difference, do the following comparison. Compare your query:

SELECT AVG(var1) AS var1_average, AVG(var2) AS var2_average,
       (AVG(var1) + AVG(var2))/2.0 AS total_average
FROM readings;

To:

SELECT AVG(var1) AS var1_average, AVG(var2) AS var2_average
FROM readings;

You'll probably find that they take about the same time. If not, you might be able to use the subquery approach. Or you might decide to do the average calculation at the application level.

Upvotes: 3

Stephan
Stephan

Reputation: 8090

Try to compare the execution times between:

SELECT 
AVG(var1) AS var1_average,
AVG(var2) AS var2_average,
(
 AVG(var1) + 
 AVG(var2)
)/2.0 AS total_average
FROM readings

And:

SELECT
    (var1_average+var2_average)/2.0 AS total_average
    var1_average,
    var2_average
FROM (
    SELECT 
        AVG(var1) AS var1_average,
        AVG(var2) AS var2_average
    FROM readings
) as tmp

Make sure to run them multiple times and also add SQL_NO_CACHE to get meaningful results.

NOTE: The second query should have a small overhead since mysql will create a temp table but this will be irrelevant if there are a lot of records in readings table.

Upvotes: 1

Related Questions