Key Takeaways
- MySQL, the open source database platform, has grown significantly in recent years, now boasting over 20 million total downloads, 4 million active installations, and nearly 35,000 downloads daily. Major users include Yahoo!, Google, Cisco, NASA, and Lufthansa.
- The current stable version, MySQL 4.01, has shown strong performance in benchmarks against industry heavyweights such as IBM, Microsoft, and Oracle. However, limitations such as the lack of subqueries and stored procedures have emerged as barriers to wider acceptance.
- MySQL’s unique licensing offers a freely downloadable version under the GPL open source license, and a commercial license for those needing support and warranties. The platform’s features include the InnoDB for transactional database efforts and the ability to use both InnoDB and ISAM for non-transaction needs in the same database.
- Future developments for MySQL include the addition of subqueries in version 4.1, and the introduction of stored procedures in version 5.0. A new product, MySQL Cluster, which promises 99.999% uptime availability, has also been announced and is currently available for preview.
MySQL has long been known as the Web developers’ database platform. In fact, for many years, it was considered the stepchild of “real” relational database systems such as IBM’s DB2, Oracle and Microsoft’s SQL Server. However, in recent years, MySQL has grown in leaps and bounds both from the development side as well as in the eyes of developers and corporate IT staff internationally.
Developed partially in the 1980s, with the SQL portion built in 1995, its core developer, Michael Widenius (along with David Axmark and Allan Larsson), also founded MySQL AB. The current stable release is version 4.01.
MySQL is, of course, also an open source solution. According to Arjen Lentz of MySQL AB, the free and open source model is the best — it involves public scrutiny of the code, third party security audits, feedback throughout the development process, and battleÂtesting in many different environments.
MySQL’s penetration into the international market is vast, including more than 20 million total downloads, 4 million active installations and nearly 35,000 downloads daily. Included in this user base are such names as Yahoo!, Google, Cisco, NASA, Dunn & Bradstreet, Lufthansa, Hyperion and Sabre Holdings.
The licensing is unique as well, with a freely downloadable version available under the GPL open source license, and a commercial license available for those who need support and want warranties.
Where We Are Now? Version 4.0x (Production)
MySQL has been ideal in the Java, Perl and PHP Web development environments for some time. At individual site level, and at enterprise level, powerful Web applications have found success running on the MySQL database engine. Its capabilities were further reinforced through recent public benchmarks that compared MySQL’s performance against the industry heavyweights including IBM, Microsoft and Oracle.
The surprise to many non-adopters of the open source database was that, performance-wise, MySQL ran neck and neck with Oracle and outperformed the others in the relentless tests carried out by eWeek’s Test Labs.
There are some limitations in the current stable version, two of the most significant being subqueries and stored procedures. As Web applications become more sophisticated, these issues have emerged as barriers to wider acceptance of the platform. Some firms, such as NASA, have worked around the limitations using external scripting such as Perl.
However, there are also additions to the 4.0x production version that have helped position MySQL as a serious player in the database engine market. Significant among these changes were the inclusion of the InnoDB for transactional database efforts and, as eWeek noted, the ability to use both InnoDB and ISAM for non-transaction needs in the same database — an advantage that’s unique to MySQL. When paired with the ability to cache queries, this latter capability was the driver behind MySQL’s impressive performance. This cache feature allows the results of large queries to be stored in memory, enabling later queries to return results almost instantly.
Additional functionality, such as database replication, rounds out the feature set that makes MySQL the primary choice for Web app development.
Note that The MySQL Manual online has all the documentation necessary to set up and utilize the features discussed in this article. Additionally, if you plan to experiment with version 4.1 or later, be sure to use a development environment, as some changes cannot be downgraded after installation and grant updates are completed.
Why the InnoDB?
The InnoDB provides a transaction storage environment including commit, rollback and crash-recovery. This is extremely useful in Web applications that are used by multiple users concurrently, such as order management or CRM solutions. The creation of InnoDB tables is simple, as is illustrated by the addition to the create table statement seen below.
CREATE TABLE prospects (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;
The InnoDB also establishes its own dictionary according to MySQL’s documentation, so you can have numerous tables of the same name (i.e. clients, customers, orders, and so on) in databases that use InnoDB.
Finally, InnoDB allows for mixing of queries on InnoDB and non-InnoDB tables within the same sql, and of course performance is optimized when used in conjunction with the query cache.
The Query Cache
The query cache is most useful in the Web environment. Database-driven sites are primarily dynamic pages generated from database content. The cache can significantly improve the performance of these environments.
If you do not have the query cache turned on, you can do so as a test by simply using mysqld --query_cache_size=10m
, which sets the query cache to 10MB (use k for KB and g for GB). You can configure this to start at boot of MySQL using the set command:
mysql> SET query_cache_size = 10240;
Replicating Databases
Using the new MySQL Administrator, replication can now be managed through a graphical user interface as well as the mysql command line. Replication can be used as a backup tool, as a load-balancing tool, or as a migration tool when moving to new hardware (and possibly a new hosting environment where IPs are changing).
Considering the numerous options and changes in replication privileges as new versions are released, a discussion of the topic is beyond the scope of this article. See the replication documentation on the MySQL site for more information.
The Next Step – Version 4.1 (alpha)
The primary identifying addition to this release is subqueries. Subqueries are simply select statements nested within existing select statements. Unfortunately, in versions prior to 4.1, subqueries either needed to be avoided or required the development of somewhat complex joins and/or unions. Simply, subqueries are easier to write and read when reviewing an sql.
A simple yet powerful example of a subquery that cannot be accomplished with a join is show below:
SELECT partner_name, number_clients_referred
FROM partners
WHERE number_clients_referred = (SELECT MAX(number_clients_referred) FROM employees);
This if course finds all of the values in the table ‘partners’ that equal a maximum value in the table called ’employees’.
The “Golden Egg” – Version 5.0
Version 5.0 is currently under development, but in the preview, the one substantial feature that’s present in the known relational database systems is stored procedures. A stored procedure (sp) allows for a collection of sql statements to be stored in one command and called. These commands run on the server-side only and can be useful as they don’t require client side interaction once they’re called. Additionally, sps can increase data security and integrity.
Sps can be used for activities as simple as data validation on multiple raw imports of external data being processed and loaded into a database. They’re also useful to carry out sophisticated report generation, returning to the client side results that cannot be handled in a single sql statement.
Data Validation
For example, imagine you’re developing a yellow pages Web application, and receive business data from numerous sources, which is then aggregated and loaded into a single database. In this case, you could use an sp to validate that all Website and email addresses adhere to your proper format, and all phone numbers appear in the correct format, with dashes to separate country code, area code, prefix and so on.
Report Generation
Suppose you’re deploying a reporting solution that compiles reports for investment advisors who wish to see their clients’ accounts and transactions, as well as the data calculated to show commissions and the rate of return on investments. A stored procedure will enable you to wrap these various sql statements into one object that can result in a complete online statement for viewing and distribution.
MySQL Cluster
Just announced at the MySQL Users Conference in Florida, MySQL has released a preview of MySQL Cluster. The system claims 99.999% uptime availability and seeks to enter the market now held predominantly by firms such as Oracle, IBM and Microsoft’s sql servers.
As I noted in Open Sourcery on the day of the announcement, one of the key factors for Web developers is the dual licensing (GPL open source and commercially supported license). This enables a Web developer to download and build a test environment of MySQL Cluster without any licensing costs (which is impossible with other vendors!) as a proof of concept for current and potential customers.
While the system is being touted as one for massive installations such as current MySQL customers NASA, Sabre Holdings and others, the ability to bring high-level clustering to any firm that needs critical uptime for commerce or extranets is substantial.
Though the current download offers preview mode only, MySQL has been working behind the scenes to build custom clusters for many of their existing enterprise clients. By bringing the product to preview, the open source community at large will be able to review, comment and recommend features in the evolution of this new product.
Two papers are now available from MySQL. One serves as an introductory whitepaper; the other provides a technical overview of the new MySQL Cluster architecture.
- Introductory whitepaper – http://www.practicalapplications.net/kb/mysql-cluster-whitepaper.pdf
- Architecture Overview – http://www.practicalapplications.net/kb/mysql_cluster_architecture.pdf
Wrapping Up
MySQL has surely established itself as the quintessential database for Web development. Furthermore, it is a rising star in enterprise environments where data sizes in the terabytes are common. MySQL has been shown to handle 50 million records per table and will only improve its scalability in release 5.
If you have not yet experimented with MySQL, a great place to start is with Kevin Yank’s tutorial series on using MySQL and PHP in the development of dynamic Websites.
Frequently Asked Questions about MySQL Roadmap
What is the significance of MySQL Roadmap?
The MySQL Roadmap is a strategic plan that outlines the future developments and improvements of the MySQL database management system. It provides a clear direction on the new features, enhancements, and updates that are expected to be introduced in the upcoming versions of MySQL. This roadmap is crucial for developers, database administrators, and businesses as it helps them plan their projects and strategies accordingly. It also gives them an idea of what to expect in terms of performance, security, and functionality improvements in MySQL.
How often is the MySQL Roadmap updated?
The MySQL Roadmap is updated regularly to reflect the latest plans and strategies of the MySQL development team. The frequency of updates can vary depending on various factors such as the complexity of the planned features, feedback from the user community, and changes in the technology landscape. However, it’s generally updated at least once a year.
Where can I find the latest MySQL Roadmap?
The latest MySQL Roadmap can be found on the official MySQL website. It’s usually published in the form of a document or a web page that provides detailed information about the planned features and improvements for the upcoming versions of MySQL. It’s important to check the roadmap regularly to stay updated with the latest plans and strategies of the MySQL development team.
What are some key features to look forward to in the MySQL Roadmap?
The MySQL Roadmap typically includes a variety of features and improvements aimed at enhancing the performance, security, and functionality of the MySQL database management system. Some key features to look forward to could include advanced data analytics capabilities, improved scalability and performance, enhanced security features, and better support for cloud-based deployments. However, the specific features can vary depending on the roadmap.
How can I contribute to the MySQL Roadmap?
The MySQL community plays a crucial role in shaping the MySQL Roadmap. Developers, database administrators, and users can contribute by providing feedback, suggesting new features, and reporting bugs. This can be done through various channels such as the MySQL forums, mailing lists, and bug tracking system. The MySQL development team values this input and often incorporates it into the roadmap.
How does the MySQL Roadmap compare to other SQL roadmaps?
The MySQL Roadmap, like other SQL roadmaps, outlines the future developments and improvements of the database management system. However, the specific features and strategies can vary depending on the focus of the development team. For instance, some roadmaps might focus more on performance improvements, while others might prioritize security enhancements or new functionalities.
What is the impact of the MySQL Roadmap on businesses?
The MySQL Roadmap can have a significant impact on businesses. It provides them with a clear direction on the future developments of MySQL, allowing them to plan their projects and strategies accordingly. For instance, if the roadmap indicates that a certain feature will be introduced in the next version of MySQL, businesses can start preparing for it in advance.
How reliable is the MySQL Roadmap?
The MySQL Roadmap is a strategic plan that outlines the future developments of MySQL. While it’s generally reliable, it’s important to note that it’s a plan and not a guarantee. The actual developments can vary depending on various factors such as technical challenges, changes in the technology landscape, and feedback from the user community.
How does the MySQL Roadmap affect developers?
The MySQL Roadmap provides developers with a clear direction on the future developments of MySQL. This can help them plan their projects and strategies accordingly. For instance, if the roadmap indicates that a certain feature will be introduced in the next version of MySQL, developers can start preparing for it in advance.
How can I stay updated with the latest MySQL Roadmap?
The best way to stay updated with the latest MySQL Roadmap is to check the official MySQL website regularly. You can also subscribe to the MySQL mailing list or follow MySQL on social media to receive updates and announcements.