mSQL Versions (MySQL & mSQL)

5.2. mSQL Versions

When mSQL arrived on the scene, it made an immediate impact on the development community. For the first time, people had access to an affordable, SQL-based database engine. Among its more amazing aspects was that it not only compared with the major database engines in terms of performance, but that it was actually faster -- sometimes over 100 times faster -- in the areas for which it was designed.

Today, however, the computing environment does not stay still for long. With the advent of large-scale Internet collaboration, no project is beyond the reach of a dedicated base of programmers. By 1996, other cheap SQL implementations -- one of which is MySQL -- were appearing on the scene. mSQL was no longer alone.

Throughout the 1990s, Hughes has been developing and improving mSQL. The database engine, however, eventually reached the point where further development required some extensive rethinking of the entire project. Such a huge undertaking was bound to take a great deal of time as any new project has its share of new bugs and setbacks. During this time, it would also be necessary to maintain the existing product. mSQL 2 was thus born as the new rebuild of the mSQL engine while the existing product, mSQL 1, continued to be maintained.

mSQL 2 came along when the initial product was beginning to show its age. Stability problems and lack of important functionality, such as the support of important datatypes, were leading people to look for other solutions like MySQL. mSQL 2 provided fixes for a large range of bugs that plagued later releases of mSQL 1 and added a host of new features while remaining true to its original design goals. The subset of ANSI SQL supported by mSQL grew and a number of new datatypes were added. The indexing feature was reworked to provide much more powerful table indexing.

The major changes between mSQL 1 and 2 are:

Increased stability and performance: The initial release of mSQL 2 fixed all of the known stability problems of mSQL 1. Memory leaks were eliminated and the code was extensively profiled to remove bugs. In addition, overall performance increased in spite of the new features.
Improved indexing support: The first version of mSQL implemented a very weak indexing scheme. Each table could have exactly one index made up of exactly one column -- the primary key. Indexing has been completely rewritten for mSQL 2 to support more complex and common indexing needs. You can now have multiple indices per table, and an index can be made up of more than one column. Indexing now also supports both B-Tree and AVL style index files.
More datatypes: mSQL 2 has added many new datatypes, bringing it close to a complete implementation of the ANSI SQL2 specification. Along with MONEY, DATE, and TIME types, mSQL 2 now supports a TEXT datatype. In the initial mSQL release, all fields were fixed length so that all text fields -- type CHAR -- had their lengths predefined. In order to support common attributes, such as email addresses or book titles, you had to define a large CHAR field that largely wasted space for a majority of addresses or titles. For example, for an email address field, you would have to define a CHAR(35) field. Even if your email address was "xxx@imaginary.com," mSQL used a full 35 characters for the entry. Furthermore, if you encountered an email address that was longer than 35 characters, you were out of luck. The new TEXT datatype takes care of both problems by enabling you to define an average length for the field. Anything over that length will be stored in an overflow buffer. Anything less will not cause extra characters to be stored. Unfortunately, TEXT fields are still lacking in that they cannot serve as indices and cannot be used in LIKE clauses.
Enhanced tools and API support: The standard tools provided with mSQL have been enhanced to support all of the new mSQL features. Hughes has added new functionality to the tools, such as the table copy and rename feature in msqladmin. The World Wide Web interactivity application W3-mSQL has been given a major overhaul with many enhancements. The scripting language has been reworked into Lite, a language with features that directly support web/database interactivity.

If you are new to mSQL, you almost certainly will be working with mSQL 2. If you are dealing with a legacy system, however, you should be very conscious of the distinctions between the two versions -- especially if you intend to upgrade to mSQL 2.