In the world of databases that work with PHP, MYSQL 4 has been the norm for quite some time now. It functions well, has decent performance and generally does what it is supposed to do. But as with all things, its time is passing and it is time to upgrade to its successor, MYSQL 5. There are many reasons to upgrade to MYSQL 5, the main reason being stored procedures, triggers, and views.
Lets first take a look at stored procedures. Stored procedures have many benefits in terms of performance and security. A stored procedure is a procedure that is that is stored in a database. This is sort of like a function or sub-routine in a regular computer language. Like functions in other languages, stored procedures come in two flavors, those that you call to perform a task or those that you call to return a value that you use in another part of the query. The main reason why you should use stored procedures is because of speed. Stored procedures are stored on the server so if you need to do a certain task over and over again, stored procedures will give great boosts in speed and also put less of a burden on the server because it will not need to send messages back and forth to the client machine since its all on the server side. Another advantage is that stored procedures are common throughout all MYSQL deployments. You never have to install an extension class or add-on feature to any MYSQL setup to get stored procedures to work. Not only will they work in all deployments of MYSQL, but since MYSQL uses standard SQL syntax and stored procedures are largely the same throughout any SQL database, you should be able to port your stored procedure code easily into either MSSQL, Oracle 10g or any other SQL based system.
The second advantage we are going to look at in MYSQL 5 are triggers. A trigger is a piece of procedural code that is automatically executed in response to certain events that happen on a table or in a database. Triggers give programmers more control over what happens when a specific alteration is made to a table. It also can restrict access to specific data, perform logging, or audit data modifications.
The final advantage that we will examine here are views. Views are a handy programming tool that MYSQL 5 developers can use to show actual code directly from the screen of the client program. This is useful for debugging and troubleshooting. Views are also often used to help write queries. There have been some performance problems with using the view function, but those will soon be resolved.
With these three advantages, MYSQL 5 is definitely a step beyond MYSQL 4 and worth an upgrade now.
