Optimizing repeated subexpressions in MySQL

January 23, 2009

Author

Baron Schwartz

Insight for Developers

Share this Post:

How smart is the MySQL optimizer? If it sees an expression repeated many times, does it realize they’re all the same and not calculate the result for each of them?

I had a specific case where I needed to find out for sure, so I made a little benchmark. The query looks something like this:

<br>select sql_no_cache<br>   pow(sum(rental_id), 1),<br>   pow(sum(rental_id), 2),<br>   pow(sum(rental_id), 3),<br>   pow(sum(rental_id), 4),<br>   pow(sum(rental_id), 5),<br>   pow(sum(rental_id), 6),<br>   pow(sum(rental_id), 7),<br>   pow(sum(rental_id), 8),<br>   pow(sum(rental_id), 10),<br>   pow(sum(rental_id), 11),<br>   pow(sum(rental_id), 12),<br>   pow(sum(rental_id), 13),<br>   pow(sum(rental_id), 14),<br>   pow(sum(rental_id), 15),<br>   pow(sum(rental_id), 16),<br>   pow(sum(rental_id), 17),<br>   pow(sum(rental_id), 18)<br>   from sakila.rental;<br>

1

select sql_no_cache pow(sum(rental_id), 1), pow(sum(rental_id), 2), pow(sum(rental_id), 3), pow(sum(rental_id), 4), pow(sum(rental_id), 5), pow(sum(rental_id), 6), pow(sum(rental_id), 7), pow(sum(rental_id), 8), pow(sum(rental_id), 10), pow(sum(rental_id), 11), pow(sum(rental_id), 12), pow(sum(rental_id), 13), pow(sum(rental_id), 14), pow(sum(rental_id), 15), pow(sum(rental_id), 16), pow(sum(rental_id), 17), pow(sum(rental_id), 18) from sakila.rental;

I wrote a bash script to execute the query 500 times and redirect the results to /dev/null. Results: 31s wall-clock time.

The following is an alternative query that returns the same result:

<br>select sql_no_cache<br>   pow(@sum := (select sum(rental_id) from sakila.rental), 1),<br>   pow(@sum, 2),<br>   pow(@sum, 3),<br>   pow(@sum, 4),<br>   pow(@sum, 5),<br>   pow(@sum, 6),<br>   pow(@sum, 7),<br>   pow(@sum, 8),<br>   pow(@sum, 10),<br>   pow(@sum, 11),<br>   pow(@sum, 12),<br>   pow(@sum, 13),<br>   pow(@sum, 14),<br>   pow(@sum, 15),<br>   pow(@sum, 16),<br>   pow(@sum, 17),<br>   pow(@sum, 18);<br>

1

select sql_no_cache pow(@sum := (select sum(rental_id) from sakila.rental), 1), pow(@sum, 2), pow(@sum, 3), pow(@sum, 4), pow(@sum, 5), pow(@sum, 6), pow(@sum, 7), pow(@sum, 8), pow(@sum, 10), pow(@sum, 11), pow(@sum, 12), pow(@sum, 13), pow(@sum, 14), pow(@sum, 15), pow(@sum, 16), pow(@sum, 17), pow(@sum, 18);

This query runs 500 iterations in 12s. So it looks like the optimizer was computing the SUM() 18 times in the first query. (A response on the internals mailing list agreed with this finding. Thanks Konstantin.)

Along the same lines, I benchmarked POW() and found that squaring a number is more than twice as fast to do with ordinary multiplication as with POW(). Does this matter? Sometimes… we had a client who was doing vector math in a many-dimensional space, and the POW() seemed to be taking a pretty significant amount of time; changing to ordinary multiplication made the query faster. (It was not the ultimate suggestion though.)