Improved scalability on multi-core CPUs. The trend in hardware development now is toward more cores rather than continued increases in CPU clock speeds, which renders “wait until CPUs get faster” a nonviable means of improving database performance. Instead, it is necessary to make better use of multiple cores to maximally exploit the processing cycles they make available. MySQL 5.5 takes advantage of features of SMP systems and tries to eliminate bottlenecks in MySQL architecture that hinder full use of multiple cores. The focus has been on InnoDB, especially locking and memory management.

InnoDB I/O subsystem changes enable more effective use of available I/O capacity.

Several modifications improve operation of MySQL Server on Solaris.

There is better access to execution and performance information. Diagnostic improvements include DTrace probes, expanded SHOW ENGINE INNODB STATUS output, and a new status variable.

Support for an interface for semisynchronous replication: A commit performed on the master side blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction. Semisynchronous replication is implemented through an optional plugin component. See Section 16.3.8, “Semisynchronous Replication”

Support for the SQL standard SIGNAL and RESIGNAL statements. See Section 12.7.8, “SIGNAL and RESIGNAL”.

Support for Performance Schema, a feature for monitoring MySQL Server execution at a low level. See Chapter 20, MySQL Performance Schema.

Support for additional Unicode character sets: utf16, utf32, and utf8mb4. These character sets support supplementary Unicode characters; that is, characters outside the Basic Multilingual Plane (BMP). See Section 9.1.10, “Unicode Support”.

Enhancements to XML functionality, including a new LOAD XML statement.

Two new types of user-defined partitioning are supported: RANGE COLUMNS partitioning is an extension to RANGE partitioning; LIST COLUMNS partitioning is an extension to LIST partitioning. Each of these extensions provides two enhancements to MySQL partitioning capabilities:

It is possible to define partitioning ranges or lists based on DATE, DATETIME, or string values (such as CHAR or VARCHAR).

You can also define ranges or lists based on multiple column values when partitioning tables by RANGE COLUMNS or LIST COLUMNS, respectively. Such a range or list may refer to up to 16 columns.

For tables defined using these partitioning types, partition pruning can now optimize queries with WHERE conditions that use multiple comparisons between (different) column values and constants, such as a = 10 AND b > 5 or a < “2005-11-25″ AND b = 10 AND c = 50.

For more information, see Section 17.2.1, “RANGE Partitioning”, and Section 17.2.2, “LIST Partitioning”.

It is now possible to delete all rows from one or more partitions of a partitioned table using the ALTER TABLE … TRUNCATE PARTITION statement. Executing the statement deletes rows without affecting the structure of the table. The partitions named in the TRUNCATE PARTITION clause do not have to be contiguous.

Key caches are now supported for indexes on partitioned MyISAM tables, using the CACHE INDEX and LOAD INDEX INTO CACHE statements. In addition, a key cache can be defined for and loaded with indexes from an entire partitioned table, or for one or more partitions. In the latter case, the partitions are not required to be contiguous.

The TO_SECONDS() function is added. This function converts a date or datetime expression to a number of seconds since the year 0. You may use this function in partitioning expressions, and partition pruning is supported for tables defined using such expressions.

The following constructs are obsolete and have been removed in MySQL 5.5. Where alternatives are shown, applications should be updated to use them.

The log_bin_trust_routine_creators system variable (use log_bin_trust_function_creators).

The myisam_max_extra_sort_file_size system variable.

The record_buffer system variable (use read_buffer_size).

The sql_log_update system variable.

The table_type system variable (use storage_engine).

The FRAC_SECOND modifier for the TIMESTAMPADD() function.

The TYPE table option to specify the storage engine for CREATE TABLE or ALTER TABLE (use ENGINE).

The SHOW TABLE TYPES SQL statement (use SHOW ENGINES).

The SHOW INNODB STATUS and SHOW MUTEX STATUS SQL statements (use SHOW ENGINE INNODB STATUS SHOW ENGINE INNODB MUTEX).

The SHOW PLUGIN SQL statement (use SHOW PLUGINS).

The LOAD TABLE … FROM MASTER and LOAD DATA FROM MASTER SQL statements (use mysqldump or mysqlhotcopy to dump tables and mysql to reload dump files).

The BACKUP TABLE and RESTORE TABLE SQL statements (use mysqldump or mysqlhotcopy to dump tables and mysql to reload dump files).

TIMESTAMP(N) data type: The ability to specify a display width of N (use without N).

The –default-character-set and –default-collation server options (use –character-set-server and –collation-server).

The –delay-key-write-for-all-tables server option (use –delay-key-write=ALL).

The –enable-locking and –skip-locking server options (use –external-locking and –skip-external-locking).

The –log-bin-trust-routine-creators server option (use –log-bin-trust-function-creators).

The –log-long-format server option.

The –log-update server option.

The –master-xxx server options to set replication parameters (use the CHANGE MASTER TO statement instead): –master-host, –master-user, –master-password, –master-port, –master-connect-retry, –master-ssl, –master-ssl-ca, –master-ssl-capath, –master-ssl-cert, –master-ssl-cipher, –master-ssl-key.

The –safe-show-database server option.

The –skip-symlink and –use-symbolic-links server options (use –skip-symbolic-links and –symbolic-links).

The –sql-bin-update-same server option.

The –warnings server option (use –log-warnings).

The –no-named-commands option for mysql (use –skip-named-commands

The –no-pager option for mysql (use –skip-pager).

The –no-tee option for mysql (use –skip-tee).

The –position option for mysqlbinlog (use –start-position).

The –all option for mysqldump (use –create-options).

The –first-slave option for mysqldump (use –lock-all-tables).

The –config-file option for mysqld_multi (use –defaults-extra-file).

The –set-variable=var_name=value and -O var_name=value general-purpose options for setting program variables (use –var_name=value).