Manage Learn to apply best practices and optimize your operations.

Reasons to consider why you should upgrade MySQL 5

If a company is running MySQL 4 now and is happy with it, why should it consider moving up to 5.0?
For a large company this question is a combination of why and when. Companies that find MySQL 4 sufficient for their needs may want to stay where they are: MySQL 4 and MySQL 4.1 are quite stable at the moment and work reliably.

Many companies even keep a policy of staying one version back from the latest release, meaning that they use Windows 2000 instead of Windows XP, MySQL 4 instead of 4.1, etc. Such a policy promotes stability over features.

So, why would you want to move to MySQL 5? The biggest three features of interest to most companies are stored procedures, triggers and views.

Stored procedures allow you to move business logic from end applications to the MySQL server, which can save effort when you have multiple end applications (instead of implementing a price calculation routine in PHP for the web and VC for the desktop, you can implement it in a stored procedure and call it from both PHP and VC). Stored procedures can also enhance security by allowing you to prevent a user from accessing a table directly, but instead giving them permission to call a stored procedure that has access to the table.

Triggers are stored routines that are written with a syntax like that of stored procedures, but instead of being called by a user or application, triggers are activated by table events such as inserts, updates, and deletes. The triggers can modify or abort the triggering table actions, or take the information provided by the action and use it elsewhere. I recently used a trigger to create a MyISAM FULLTEXT lookup table for an InnoDB table: any inserts, updates, or deletes to the InnoDB table were mirrored to the lookup table by triggers, and the end users didn't have to change anything about the way they interacted with the InnoDB table.

Views are essentially named virtual tables defined by SQL queries. I can take a complex set of tables, create a SELECT query that might be more meaningful to end users, then turn it into a view. The end users can then perform SELECT queries against the view and, in most cases, also execute INSERT, UPDATE, and DELETE statements against the view, with the changes being reflected in the underlying tables. Views help to simplify complicated data and also enhance security: as with a stored procedure, you can create a view and give a user permission to SELECT from the view but not the underlying table.

In the end most users will eventually move to MySQL 5 as MySQL 4 and 4.1 age and become outdated. The timing will have to depend on whether you consider the features of MySQL 5 to be useful to your organization.

Dig Deeper on Linux servers

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.