Are mathematical functions in MySQL faster than PHP?

Question

I've been working with MySQL for a while, but I've never really used the supported mathematical functions, such as FLOOR(), SQRT(), CRC32(), etc.

Is it faster / better to use these functions in queries rather than just doing the same on the result set with PHP?

EDIT: I don't think this question is a duplicate of this, as my question is about mathematical functions, listed on the page I linked, not CONCAT() or NOW() or any other function as in that question. Please consider this before flagging.

Answer 1

It is more efficient to do this in PHP.

Faster depends on the machines involved, if you're talking about faster for one user. If you're talking about faster for a million users hitting a website, then it's more efficient to do these calculations in PHP.

The load of a webserver running PHP is very easily distributed over a large number of machines. These machines can run in parallel, handling requests from visitors and fetching necessary information from the database. The database, however, is not easy to run in parallel. Issues such as replication or sharding are complex and can require specialty software and properly organized data to function well. These are expensive solutions compared to adding another PHP installation to a server array.

Because of this, the value of a CPU cycle on the database machine is far more valuable than one on the webserver. So you should perform these math functions on the webserver where CPU cycles are cheaper and significantly more easy to parallelize.

Answer 2

There's no general answer to that. You certainly shouldn't go out of your way to do math in SQL instead of PHP; it really doesn't make that much of a difference, if there is any. However, if you're doing an SQL query anyway, and you have the choice of doing the operation in PHP before you send it to MySQL or in the query itself... it still won't make much of a difference. Oftentimes there will be a logical difference, in terms of when and how often exactly the operation is performed and where it needs to be performed and where the code to do this is best kept for good maintainability and reuse. That should be your first consideration, not performance.

Overall, you have to do some really complex math for any of it to make any difference whatsoever. Some simple math operations are trivial in virtually any language and environment. If in doubt, benchmark your specific case.