To keep my question short and to the point, consider this:

$relation = $mysql->query("SELECT relation($loggeduser, $requesteduser) AS a")->fetch_assoc()['a'];
if ($relation != BLOCKED)
    return $mysql->query("SELECT stuff FROM sometable WHERE powerlevel>9000 LIMIT 39");
} else {
    return '{"error":"not enough jquery"}';

Where relation is some MySQL function. When would this^ approach be better than something like this:

$stuff = $mysql->query("SELECT getstuff($loggeduser, $requesteduser) AS a")->fetch_assoc()['a'];
return ($stuff != null) ? $stuff : '{"error":"stop it"}';

Where getstuff is some MySQL function that just does everything that the first code would do.

In this scenario, the second seems to make a lot more sense. In reality, the second makes sense for so many other scenarios I can think of to the point where you can have a web app essentially composed of just MySQL functions that deal with almost everything you need (i.e. getUsers, getFollowers, pageInfo, etc.).

However, I'm surely missing something, and my question is this - When should I not use MySQL functions over server-sided scripting operations? You can omit the obvious (i.e. saving/processing files, etc.).

marked as duplicate by GlenH7, gnat, user40980, Bart van Ingen Schenau, Dan Pichelman Dec 29 '14 at 15:55

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.


The second first approach makes another round-trip to the database. I suppose it might let you run some complex processing on the web-server side.

If that additional processing is indeed complex, it's often preferable to keep it outside the database. Database stored procedure languages are often not the best at performance, and stored function code is usually harder to properly modularize, test, source-control and deploy.

If you already have all the (reasonably simple) processing in your relation function, you're better off using it right in the query and saving a database round-trip.

  • Why would the second approach make another round-trip to the database? I thought the first one did. – Shahar Dec 23 '14 at 23:21
  • @Shahar: right; fixed. – 9000 Dec 24 '14 at 16:22

Not the answer you're looking for? Browse other questions tagged or ask your own question.