I installed a wordpress blog and was tinkering with the database,
I noticed they are not using any sotred procedures or views why is this?
Or is it just not available for wordpress.org users and some premium feature for paid wordpress.com members?
Is it not advisable to use these to improve performance considering wordpress stores almost everything except media files in database.
Are there any resources / attempts to optimize wp database using these ?
The decision regarding where to keep transformations of / operations on data is heavily rooted in the concept of what you consider to be the central interface to the data within the application as a whole.
If you’re a database programmer, you’re much more likely to consider that central point to be the database. In this view, the data is the center, and the surrounding application can be thought of as just an interface on top of that data. This view makes sense when dealing with anything where data itself is key. I.e., where the data will stay put over time, and the ways in which the data is accessed, or the things which you want to do with the data will change over time. Examples which fit well into this view include: Financial systems, Healthcare records, Customer data, Phone records… pretty much anything that has a lot of ways of looking at the data, and is constantly growing.
If you’re an application programmer, the data itself may be almost secondary. In this view, the data is transient. Where and how that data is stored is even less important. The MVC pattern encourages the database to be utterly replaceable, and strongly discourages putting any sort of logic related to anything other than basic data integrity into the the database. There is certainly nothing about the MVC pattern or other application-centric development practices which argue specifically against stored procedures or views, but there is much less room for them to be useful. Examples which fit well into this view inclue: Blogs, Message-boards, Stand-alone Documents… pretty much anything that has a very simple structure, does not have complex relations, and can be divided easily into self-contained units. Anything for which “what you can do” is tied closely in concept to “what you are doing it to”.
A summary of the two above-mentioned viewpoints is that there are tools for which examining data is more important (data-centric), and there are tools for which creating data is more important (application-centric).
Another way of looking at it is that Stored Procedures and Views are just interfaces on top of a database. WordPress is also an interface on top of a database, it’s just written in PHP.
Well, I don’t know their rationale for a fact but my guess would be that since MySQL actually stores the procedures in the “mysql” database – not the wordpress database where the tables are – that they did it because it can be an access issue. Let’s say you have a DB server supporting multiple WP databases. All the procedures get put into the “mysql” database. So when you backup your WP database you don’t get any of the procedures. You’d need to back up the mysql (system) database, and its likely the users would not have the rights to do so in such an environment, which is the typical environment for WP installs.
Excellent answers. To add, I think that from a plugin coding side, it is easier to update just the file system and do as little database work on an as needed basis.
Especially if a plugin update doesn’t install right the first time and you have to restore the files and try again, a database change would be a lot more difficult to reverse.