MySQL Views just aren’t “there yet”….

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.

In a vacuum, I completely agree that views and stored procedures should be an essential tool in the developer’s toolbox.
However, in reality, MySQL’s implementation of views leaves a lot to be desired.  MySQL views have a tremendous amount of overhead associated with them and using a PHP-side join query, no matter how complex, will result in significantly better performance than a MySQL view.
This article has much more to say on View performance in MySQL: http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/
I like stored procedures but as with views, PHP can often do many of these transformations and calculations more quickly than the MySQL server can.  I also think you have to be wary of putting too much stuff into stored procedures and creating a confusing architecture if not careful to  heed separation of concerns.

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.

delicious | digg | reddit | facebook | technorati | stumbleupon | chatintamil

Leave a Reply

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