A MySQL Mantra

Having been in a previous incarnation an Oracle developer I kept a close eye on a site called AskTom. The Tom of AskTom is Tom Kyte who is an Oracle employee with a great passion for helping people with their Oracle problems. He's also an author of some great Oracle books which I recommned anybody developing with Oracle either directly via PL/SQL or with Oracle as the backend database read before they type a single line of code.

Tom has a mantra with regard to developing with Oracle. It goes like this.

o if you can do it in a single sql statement - do so.
o if you cannot, do it in as little PLSQL as you can.
o if you cannot, because plsql cannot do it, try some java (eg: sending an email
with an attachment)
o if you cannot do it in java (too slow, or you have some existing 3gl code
already) do it in C as an extproc.

This is great advice, at least for Oracle developers, in that it's making the most of each method at the appropriate stage. An SQL statement for example is far superior for data manipulation than say PL/SQL because Oracle has been built from the ground up to be as fast as possible when using SQL. But it's also true that the feature set is limited when it comes to decision making, this means there are often times when you need to revert to PL/SQL to get the job done. The third stage is somewhat particular to Oracle in that it has a JVM built into the database, therefore you can call Java as you would a stored procedure and execute this right inside the database.

One thing to be clear on here is we are not talking about developing whole applications in Oracle, we are talking about manipulating data, this means that we really won't be getting much passed stage 2 in 99% of the situations we might come across. The only time we might need to move on to the Java stage is if we need to interact with the outside world, Tom's email example being the obvious case (having said that in Oracle there are PL/SQL packages available to send email).

So how does this tie in with MySQL, it got me thinking about what a suitable Mantra would be for MySQL. I've come up with the following, based very much on Tom's Oracle Mantra, but it's by no means something set in stone, I'd really appreciate feedback on what people think.

o if you can do it in a single sql statement - do so.
o if you cannot, do it in as small a stored procedure as you can.
o if you cannot, because a stored procedure cannot do it, try a UDF
o if you cannot, because a UDF isn't suitable take the code out of the database

I should say with regard to the last stage it's likely that if you have exhausted the first three stages then you're most likely no longer dealing with data exclusively and therefore the code may live more comfortably in your application layer.