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.).
1
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.
2