eWEEK content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
MySQL ABs widely used open-source database, MySQL, is taking a big step forward in the corporate world with the addition of transaction support.
Until now, MySQL has been most popular as a content back end for dynamic Web sites because it is lightweight, free, fast and simple to use. Theres also a wealth of information online on how to use MySQL and how to work around some of its limitations.
Corporate Webmasters and Web developers will continue to find MySQL a good choice for storing catalog information, user names and passwords, or HTML code. eWeek has been using MySQL for the past five months as the database behind our eXcellence Awards program Web server.
MySQL 3.23 shipped at the end of January (the full version number is 3.23.33, the first stable release of the 3.23 series), two years after the previous release, Version 3.22, first shipped. The upgraded database is free, runs on Windows and Unix systems, and can be downloaded from www.mysql.com.
Transaction support is the biggest change in this release. Other open-source databases such as PostgreSQL and Borland Software Corp.s InterBase have long supported this feature (and provide more SQL language support).
Using MySQLs new Berkeley Database table type, eWeek Labs could create a transaction-safe table that let us start a transaction, make changes to data and then roll back the changes to undo our alterations.
MySQL uses page-level locking, which can result in performance problems when database hot spots happen (hot spots occur when many inserts and deletions are applied in one particular area of the database). Row-level locking is planned for the next minor release of MySQL.
MySQLs ODBC (Open Database Connectivity) driver doesnt yet support the ODBC transaction API (although MySQLs Java Database Connectivity driver does); we did our testing issuing SQL commands directly to MySQL.
We also found transaction support didnt work on two test servers running Red Hat Inc.s Red Hat Linux 7.0, although it did work on a Red Hat Linux 6.2 server.
Some Things Still Missing
MySQL continues to lag behind post- greSQL and InterBase in SQL language support because it doesnt support such key SQL features as foreign keys, views and subselects (selects in a SQL WHERE clause). All of these are very useful, and without them its difficult to run reports or perform data analysis tasks.
The database also doesnt support a SQL-based stored procedure language (C can be used to write user-defined functions, but this is a lot more work than writing a stored procedure) or common database programming features like triggers and server-side cursors.
MySQLs traditional Web market will find Version 3.23s new support for full-text indexing and memory-only tables quite useful for finding and manipulating data. The new FULLTEXT index type and MATCH SQL keyword let us search through text columns to find the top-ranked rows for a set of keywords.
Unfortunately, the search feature doesnt support word stemming (stemming compensates for plurality or tense differences between search terms and content), something that higher-end databases from IBM, Microsoft Corp. and Oracle Corp. do provide. The search engine also didnt let us use OR or NOT Boolean search operators, features many Web users have come to expect. Boolean operators are expected in MySQL 4.0.
Full-text indexing also works only on the native MySQL MyISAM table type, which doesnt support transactions.
A new HEAP table type let us create a very fast in-memory table suitable for caching existing data or as a temporary table. Its contents were automatically erased when we stopped the server.
On the plus side, MySQL supports one-way (master-to-slave) replication, useful for creating standby backup servers. The backup servers can be used for read-only queries, and MySQL developers are working on a mechanism for a future release to automatically distribute queries to groups of slave servers for load balancing.
MySQL 3
.23.33″>
MySQL 3.23.33
|
USABILITY |
B |
|
CAPABILITY |
C |
|
PERFORMANCE |
B |
|
INTEROPERABILITY |
B |
|
MANAGEABILITY |
B |
Organizations that want to use MySQL to manage business-critical data can now do so with more confidence because MySQL ABs open-source database offers new transaction support. It still lacks important SQL language capabilities, however, and so it is not as suitable as a general-purpose database server as are some competitors.
SHORT-TERM BUSINESS IMPACT // Built-in replication makes it much easier to have a warm standby system in case of server failure.
LONG-TERM BUSINESS IMPACT // Transaction support means companies using MySQL for some less-critical tasks will now be able to use it for more-critical transactional applications as well.
Lightweight, fast database engine; transaction support with page-level locking; full-text indexing; one-way replication; open source; free.
Standard SQL features such as foreign keys, subselects and views are missing; no stored procedures, triggers or server-side cursors; full-text indexing doesnt support word stemming or Boolean search operators.
MySQL AB, Stockholm, Sweden; info@mysql.com