New features in MySQL 4.0
MySQL 4.0 is the latest production release of the MySQL database, and it is a huge improvement over the old version 3.23. Version 4.0.12 was pronounced stable in March, 2003. Here are the main new features:
Perhaps the most-awaited enhancement is to the FULLTEXT index. This creates an index on a text field, and provides a powerful, flexible mechanism for performing boolean searches on this. In my experience, I've often had to index and access text data, and the FULLTEXT index is much better than any of my attempts. Many solutions are limited to full words only, while the FULLTEXT index has no such limitation, allowing you to weight words or portions of words. You can read more about FULLTEXT indexes in Using Fulltext Indexes in MySQL, Part 1 and Using Fulltext indexes in MySQL, Part 2
Supports the ANSI SQL UNION statement, which combines query results into one result set.
Can perform multi-table UPDATEs and DELETEs.
There are other non-standard new statements, familiar to users of other DBMS', such as IDENTITY and TRUNCATE TABLE, as well as new functions such as FOUND_ROWS(), which return the number of records that would have been returned without a LIMIT clause.
Many of the MySQL server variables can be changed without restarting the server, which, while useful, sounds more useful than it is because a reboot will restore the old settings.
The InnoDB storage engine is now a standard feature of the server, and no longer an optional extra. InnoDb is the table type that allows ACID-compliant transactions, as opposed to the default MyISAM table type, which is faster for general use, but not that useful for critical operations. InnoDB tables use row level locking, which means that an update to a record only locks that one record, not the entire table. Locking the entire table is faster when most of the database accesses are selects (as with most websites), but is slower when the number of inserts and updates are closer to the number of selects. For a long time, critics of MySQL had valid problems with the security and consistency of MyISAM tables, and the ACID compliant InnoDB table goes a long way to resolving these.
MySQL 4.0 can be faster in certain circumstances. This is mainly achieved through a query cache, which caches repetitive query results, although many mature applications perform their own caching on a code level. There are other minor speed improvements to certain statements as well.
MySQL comes with an Embededded Server library, allowing applications to be deployed with MySQL as the underlying database, such as on CD's, kiosks, fridges or even pencil sharpeners (yes a data storing pencil sharpener is coming soon, mark my words).
MySQL now supports an extra character set - latin1_de, which ensures correct sorting of German words.
MyISAM tables now support symbolic linking at a table level, so Windows users can create symlinks at a table level (this was always available to Unix users)
The security model has been enhanced, allowing the administrator to grant permissions more finely. New permissions are those allowing users to create temporary tables, lock tables, perform certain replication tasks, view all existing databases, connect even when the maximum connections have been reached (useful for a DBA to enter and perform emergency surgery) and even permission to run stored procedures (which are only coming in version 5). A DBA can also limit users to a certain number of connections, updates or queries per hour.
MySQL 4 runs natively on Novell Netware 6.0.
That's quite a list, and of course each of the versions to-date have changelogs that are at least as long as the above lists, with minor bugfixes and feature additions. However, the main two are the ACID compliancy as a default feature (many DBA's scorn to touch systems that cannot support this), and the FULLTEXT indexes.
________
Cumshot blonde
MySQL 4.0 is the latest production release of the MySQL database, and it is a huge improvement over the old version 3.23. Version 4.0.12 was pronounced stable in March, 2003. Here are the main new features:
Perhaps the most-awaited enhancement is to the FULLTEXT index. This creates an index on a text field, and provides a powerful, flexible mechanism for performing boolean searches on this. In my experience, I've often had to index and access text data, and the FULLTEXT index is much better than any of my attempts. Many solutions are limited to full words only, while the FULLTEXT index has no such limitation, allowing you to weight words or portions of words. You can read more about FULLTEXT indexes in Using Fulltext Indexes in MySQL, Part 1 and Using Fulltext indexes in MySQL, Part 2
Supports the ANSI SQL UNION statement, which combines query results into one result set.
Can perform multi-table UPDATEs and DELETEs.
There are other non-standard new statements, familiar to users of other DBMS', such as IDENTITY and TRUNCATE TABLE, as well as new functions such as FOUND_ROWS(), which return the number of records that would have been returned without a LIMIT clause.
Many of the MySQL server variables can be changed without restarting the server, which, while useful, sounds more useful than it is because a reboot will restore the old settings.
The InnoDB storage engine is now a standard feature of the server, and no longer an optional extra. InnoDb is the table type that allows ACID-compliant transactions, as opposed to the default MyISAM table type, which is faster for general use, but not that useful for critical operations. InnoDB tables use row level locking, which means that an update to a record only locks that one record, not the entire table. Locking the entire table is faster when most of the database accesses are selects (as with most websites), but is slower when the number of inserts and updates are closer to the number of selects. For a long time, critics of MySQL had valid problems with the security and consistency of MyISAM tables, and the ACID compliant InnoDB table goes a long way to resolving these.
MySQL 4.0 can be faster in certain circumstances. This is mainly achieved through a query cache, which caches repetitive query results, although many mature applications perform their own caching on a code level. There are other minor speed improvements to certain statements as well.
MySQL comes with an Embededded Server library, allowing applications to be deployed with MySQL as the underlying database, such as on CD's, kiosks, fridges or even pencil sharpeners (yes a data storing pencil sharpener is coming soon, mark my words).
MySQL now supports an extra character set - latin1_de, which ensures correct sorting of German words.
MyISAM tables now support symbolic linking at a table level, so Windows users can create symlinks at a table level (this was always available to Unix users)
The security model has been enhanced, allowing the administrator to grant permissions more finely. New permissions are those allowing users to create temporary tables, lock tables, perform certain replication tasks, view all existing databases, connect even when the maximum connections have been reached (useful for a DBA to enter and perform emergency surgery) and even permission to run stored procedures (which are only coming in version 5). A DBA can also limit users to a certain number of connections, updates or queries per hour.
MySQL 4 runs natively on Novell Netware 6.0.
That's quite a list, and of course each of the versions to-date have changelogs that are at least as long as the above lists, with minor bugfixes and feature additions. However, the main two are the ACID compliancy as a default feature (many DBA's scorn to touch systems that cannot support this), and the FULLTEXT indexes.
________
Cumshot blonde
Comment