3

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.

Community
  • 1
  • 1
kraxor
  • 649
  • 8
  • 16
  • possible duplicate of [Doing calculations in MySQL vs PHP](http://stackoverflow.com/questions/6449072/doing-calculations-in-mysql-vs-php) – adosaiguas Jun 26 '14 at 08:37
  • The use of functions in MySQL can inhibit the engine's ability to use indexes, so queries that involve comparisons on function-derived values may perform better if that derived value can be obtained 'outside' the query. Personally, I like to suck it and see - one exception being CONCAT and GROUP_CONCAT type operations, which I almost always prefer to do in application code. – Strawberry Jun 26 '14 at 09:01
  • 2
    I don't think my question is duplicate. The linked question deals with functions other than mathematical ones (`CONCAT`, `NOW()`, etc.), while I asked about **mathematical** ones, listed in the doc I linked. – kraxor Jun 26 '14 at 10:31
  • 2
    I agree with you, @kraxor, in fact I found this answer in the close vote review queue and decided not to vote. Instead, I answered the question. :) – Erick Robertson Jun 26 '14 at 14:46

4 Answers4

6

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.

Erick Robertson
  • 32,125
  • 13
  • 69
  • 98
  • This also assumes that the database isn't holding open any sort of data lock while performing the calculation. If so, then you're not just using precious CPU cycles, you're locking data from other users directly. – Erick Robertson Jun 26 '14 at 10:28
4

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.

deceze
  • 510,633
  • 85
  • 743
  • 889
  • +1 I like this answer. It's well-put and does a great job explaining why you would decide to put it one place instead of the other. – Erick Robertson Jul 12 '14 at 19:45
  • I don't believe that business logic belongs in the database, from an organizational standpoint. So I would default this to PHP unless the math was necessary to filter records out of the resultset being returned, and maybe a few other extreme cases. – Erick Robertson Jul 12 '14 at 19:46
2

Like deceze said there's probably not going to be much difference in speed between using math functions in SQL and PHP. If you're really worried, you should always benchmark both of your use cases.

However, one example that comes to my mind, when is probably better to use SQL math functions than doing PHP math functions: when you don't need to perform any additional operations on the results from the DB. If you do your operations in MySQL, you avoid having to loop through results in PHP.

There is an additional consideration to think of and that's scaling. You usually have one MySQL server (if you have more than one, then you probably already know all of this). But you can have many web servers connecting to the same MySQL server (e.g. when having load balancer).

In that case, it's going to be better to move the computation to the PHP to take the load of MySQL. It's "easier" to add more web servers than to increase the performance of MySQL. In theory you can add infinite amount of web servers, but the amount of memory and the processor speed / num. of cores in a MySQL server is finite. You can scale MySQL in some other ways, like using MySQL cluster or doing master-slave replication and reading from slaves, but that will always be more complicated / harder to do.

Christian P
  • 12,032
  • 6
  • 60
  • 71
1

MySQL is faster in scope of SQL query. PHP is faster in PHP code. If you make SQL query to find out SQRT() it should be definitely slower (unless PHP is broken) because MySQL parser and networking overhead.

Tõnu Samuel
  • 2,877
  • 2
  • 20
  • 30