I am a big fan of Jason Gilmore. His book Beginning PHP and MySQL (link is to latest version, not my actual copy) actually launched my developer career four years ago.
His latest post over on PHPbuilder.com, Refactor Your PHP Site Using MySQL Procedures and Views, has some good information about using views and stored procedures to simplify application logic and improve code maintainability.
I tried to comment to the article on PHPBuilder, but their comment deal is broken, so I thought this would make a decent blog post.
There is no question views and stored procedures are both powerful tools that MySQL went without for quite some time. However, I disagree with Jason to the extent his article fails to mention there is a cost to using these tools.
MySQL’s implementation of views has some well documented performance problems. I won’t go into them in detail here, you can read an excellent summary on the MySQL Performance Blog in a post by Peter called MySQL view as a performance troublemaker. The urge to save yourself from writing a complex query can end up costing you and your users down the road when performance issues crop up.
I like stored procedures, but SQL is slow when compared to PHP, and stored procedures will hit the database CPU more than a corresponding function in PHP. I also think you have to be wary of putting too much stuff into stored procedures and creating a confusing architecture. A good developer always heeds the rule of separation of concerns. Stuffing so much stuff into the database eventually will defeat the original purpose of simplifying code maintainability, particularly in a team environment.
If we’re talking Oracle or another of the more mature RDBMS products out there, there is no question that views should be leveraged as much as possible. While stored procedures are not as big of a performance concern, mis- or over-use can lead to architectural issues. With respect to views, MySQL just isn’t there yet.