Use string functions and arithmetic calculations in SQL query or by programming language?

Posted on

Question :

I need heavy arithmetic and string functions for processing the data captured from mysql database. Which is preferred: mysql functions or programming language (e.g., PHP)?

$result = $mysqli->query("SELECT A, B, C FROM table1");
while($row = $result->fetch_assoc() )
$a = str_replace("X", "Y", $row['A']);
$b = ($row['B']/$row['C')*1000;


$result = $mysqli->query("SELECT REPLACE(A, 'X', 'Y'), ((B/C)*1000) AS B FROM table1");
while($row = $result->fetch_assoc() )
$a = $row['A'];
$b = $row['B'];

This just a simple example with no significant difference, but when having heavy calculations, which one is preferred?

Answer :

If you’re going to bring the same amount of rows from the database anyway, then you had better perform your mathematical functions in your application, not in your database. Math calculations in SQL are notoriously slow.

Also note that this takes computation from a single database server into possibly multiple PHP front ends. Sure, you might also spread the load over multiple MySQL replication servers; generally speaking, you will have more PHP front ends than MySQL back ends.

Leave a Reply

Your email address will not be published. Required fields are marked *