Top 5 Features in MySQL 5.6

Web development experts have now been using the new version of MySQL, version 5.6. This version has various new features that ensure better performance and scalability. Top among these new features include:

1. Performance Improvements

There have been major changes aimed at optimizing performance of databases in this new version. These include:

• The index condition pushdown which moves more of WHERE clause processing to the storage engine. The storage engine then prunes the results obtained by looking at index tuples. This leads to less overhead for the base table in terms of data transfer and less internal communication with the server and storage engine.
• Multi-range read which scans several index ranges in a query in order to speed up operations such as range index scans and join operations on indexed columns.
• File sort optimization that enhances the sort speed when a query combines both the LIMIT x clause and the ORDER BY non_indexed_column clause. This feature works with all kinds of storage engines.

2. InnoDB Improvements

InnoDB is usually integrated in MySQL as the default storage engine since version 5.5. The improvements that have been added to InnoDB include:

• Persistent optimizer stats: this improves the accuracy of InnoDB index statistics and ensures consistency across multiple MySQL restarts. The storage engine calculates the statistics in advance which helps the query optimizer to decide which indexes to use. This is done by sampling a portion of the index. The amount of sampling done for each index by InnoDB can be adjusted to suit the preferences of the user. These results are saved for use even after the server restarts in order to keep the performance of the queries stable.
• New INFORMATION_SCHEMA tables: this is usually used to monitor the status of the database and has been improved to make monitoring more convenient even for large systems.
• Additional optimizer support: InnoDB provides support for the file sort, ICP and MRR optimizer features.
• Split kernel mute: this has been introduced in a bid to reduce contention on busy systems instead of using a single mute.
• Multi-threaded purge: having several purge threads make purge operations spanning several database tables more efficient.
• Separate flush thread: formerly, flushing operations were handled by the InnoDB master thread. However, placing it in a separate thread has made flushing operations even more efficient.
• Pruning down theInnoDB Table Cache: this is to save memory associated to a table that is open at the moment and there are algorithms that remove the table that have stayed in memory for the longest time without being accessed.

3. NoSQL Interface via memcached

This is very important with regard to web development. Providing NoSQL access to MySQL databases greatly improves database performance especially in situations where a web based service has many users at the same time. This has been accomplished using the memcached API which allows the developers to deploy high performance web services.

4. Scalability Improvements

As data grows bigger with time, partitioning tables splits them into manageable parts. The following features have made this even faster:

• Explicit partition selection: this restricts processing to relevant portions of the data only
• Import and export for the partitioned tables: this can swap tables without expensive copy operations

5. Replication improvements

Replication helps in improving data availability and improving performance. The improvements that have been made include:

• Optimized row-based replication
• Multi-threaded slaves: makes use of several threads to apply replication to several slave servers. This makes the replication parallel rather than sequential and improves the performance of the database
• Crash-safe slaves: the slave can easily recover from failure and automatically resume DML updates
• Replication checksum: ensure the integrity of data being replicated to slave servers
• Time-delayed replication: ensures protection against errors made on the master. This means that in case an error is made, the slave can be promoted to master as it has the latest data.

Other new features in with regards to replication include informational log events, remote bin log back-up, and server UUIDs. These greatly enhance the usability of the database.