Ludzie pragną czasami się rozstawać, żeby móc tęsknić, czekać i cieszyć się z powrotem.
The race for the fastest database engine was on. At the same time, the need for more functionality grew. DBAs needed more tools and more efficient ways to manage the large amounts of data that they were storing. The problem was that the more functionality or features that were added to a database, the slower the database became. MySQL has taken great measures to ensure it provides the maximum number of features without sacrificing speed.
How fast is MySQL? TcX has set up a Web site (www.tcx.com) that displays the differences between MySQL and other databases in its class. The Web site uses the results from a program called crashme. This program lists and compares the functionality of all the major relational database systems. TcX also has a link that displays the speed results of MySQL and other databases on several different platforms using a common interface.
In almost every category, MySQL is the fastest. In a comparison of MySQL with DB2, Informix, Sybase Enterprise 11.5, and Microsoft SQL Server all running on Windows NT and using ODBC as the common interface, MySQL outperforms all the other databases in nearly every test. For example, in a test using 20,000 SELECT statements of various complexity, MySQL accomplished these tasks nearly 50% faster than Microsoft SQL Server and nearly five times faster than Informix. According to the statistics provided, MySQL outperforms all database engines in it class by an average of 40%.
As you know, statistics can be manipulated in many ways to illustrate a point. The best way to judge MySQL is to use it. If you have ever used one of the big-name databases, you will be thoroughly impressed by what MySQL can do. If you haven't had the opportunity, you will be spoiled by the speed MySQL provides.
This is the reason why MySQL excels on the Internet. MySQL exceeds the speed requirements for the Web. Queries are executed faster and results are returned quicker. Because it works so well across the Internet, it makes a lot of sense to include MySQL in Enterprise applications as well. Imagine the flexibility MySQL could provide with its large table size and unprecedented speed.
As explained earlier, with an increase in speed comes a decrease in functionality. The developers of MySQL have done an excellent job of keeping the features-to-speed ratio relatively high. Of course they couldn't include all the features that are available in other database engines in its class. So they have selectively kept the essential features and let the optional features go by the wayside. In the near future, MySQL will include many of the features that other databases have and still retain most of its speed.
Developers will let database administrators decide whether they want to use the more advanced feature set and sacrifice speed. Giving the administrator the option is a huge benefit; it will put the decision in the hands of the administrator instead of the manufacturer. That way, the people who really know what they need can make the decision, instead of people who think they know what their users need.
- 195 -
Transaction-Based Databases
Some databases use a form of SQL called Transact-SQL or T-SQL (Microsoft SQL Server and Sybase Adaptive Server). This form of SQL extends regular SQL by adding the ability to create programs. These are not programs in the sense that they are compiled or can stand alone, but they contain the same sets of controls that most programming languages have, such as loops and IF statements. For example, a T-SQL
command set may look something like the following:
BEGIN TRAN update_orders
UPDATE Orders
SET Order_Shipped = 'Y'
WHERE Order_ID = 1454
IF @@ERROR = 0
BEGIN
COMMIT TRAN update_orders
END
ELSE
BEGIN
ROLLBACK TRAN update_orders
PRINT "Error encountered. Transaction failed"
END
As you can see, these statements are a little more complex than the SQL commands you have seen so far. The ability to perform complex routines and include logic on the database server opens the doors for numerous opportunities.
These opportunities have been added as a feature set for these databases. They include stored procedures, the ability to roll back transactions, and cursors. These features are not found in the current version of MySQL. The reasons why they are not included, as well as workarounds for you die hards, will be discussed next.
BEGIN TRANS and ROLLBACK TRANS
These databases are built on transactions. A transaction is considered to be any action that can be performed on a database. For example, every INSERT, UPDATE, or DELETE is considered a transaction, as well as any CREATE or DROP statements.
Every T-SQL command is processed by the database system before it is executed. This allows the engine to read all the commands and develop a good query plan. It will then execute the commands based on its query plan. Because the engine sees all the commands before it actually executes them, it allows for a very handy pair of features—the BEGIN TRAN and ROLLBACK TRAN.