MySQL remains a valid database option for companies looking for an alternative to Oracle or SQL Server. While it may not offer the enterprise-level horsepower of PostgreSQL, it still works well for many scenarios. This is likely why it maintains its status as the most popular open-source database in the industry.
Recently, the folks behind MySQL released version 8.0 of the database. New features and functionality abound. Let’s take a high-level overview of this new edition to see if there’s anything to help with your own software development projects.
The new version of MySQL added a whole host of enhanced SQL functionality. This includes support for window functions, common table expressions, as well as the NOWAIT and SKIP LOCKED statements. Most notably, window functions provide the ability to perform analytics on stored data; this is a long-awaited feature as SQL Server added it in 2003.
They also added support for descending indexes which provides a performance improvement, especially when working with large datasets. The new GROUPING() function lets developers build datasets that distinguish super-aggregate rows from the results of GROUP BY queries. Both of these features were highly requested among the MySQL user community.
Boosting overall application performance becomes essential when using MySQL. Version 8.0 gives developers a new syntax for including optimizer hints. You simply place them using something similar to inline comment blocks after a SELECT, INSERT, UPDATE, REPLACE, or DELETE statement.
Additionally, the new version adds optimizer hints for a variety of INDEX and JOIN statements. Now developers can control index merge behavior for each individual query or even the table order when performing a join. The MySQL development team feels the new optimizer hint syntax makes it easier to use while also boosting code readability.
The JSON format is essential for web applications that rely on transferring objects expressed as a data structure. MySQL 8.0 improves its support for JSON in a myriad of ways. First, it adds extended syntax for ranges when using JSON path expressions.
The new JSON table functions let you use regular SQL statements when working with JSON data. This is a boon for developers especially skilled in writing SQL queries. It essentially creates a relational view of JSON data.
Other new JSON features in MySQL 8.0 include aggregation and merge functions. A boost in sorting performance and the ability to perform partial updates are also welcome. The former helps to optimize large applications while the latter makes replication processes faster.
Another significant new feature in MySQL 8.0 is support for GIS, including the Spatial Reference System. This lets applications using the database easily calculate global distances given a LAT and LONG. The database now supports bitwise operations on binary data types, making the processing of IPV6 addresses easier.
The MySQL query optimizer also gets some improvements beyond the new hint syntax. Histograms and better handling of data buffering help engineers boost overall app and database performance. Finally, the database boasts improved reliability, availability, and reporting with an eye towards being used at companies following DevOps.
In short, MySQL 8.0 adds a host of new features making the database more attractive to organizations with high-demand applications. The improved DevOps support is also welcome. For more detailed information on MySQL 8.0, simply click on this link.
Thanks for reading this edition of the Betica Blog. Keep returning for additional insights on the software development world.
Posted on May 30, 2018
Categories: News, Quality Assurance, Software Development, Team
Tags: Agile, Database, dev, devnews, DevOps, MySQL, PostgreSQL, Software Development, SQL