Home arrow MySQL arrow Page 4 - A Technical Tour of MySQL

The MySQL Engine - MySQL

In this article, Vikram gives us a sneak-peek under the hood of MySQL to see what makes it tick, all the while explaining the various MySQL subsystems and how they interact with each other. This excerpt comes from Chapter two of MySQL: The Complete Reference (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004).

  1. A Technical Tour of MySQL
  2. An Overview of MySQL Architecture (cont'd)
  3. Support Components
  4. The MySQL Engine
  5. Characteristics in Detail
  6. Extensibility
  7. Symmetric Multiprocessing with MySQL
  8. Security
  9. Replication
  10. Application Programming Interfaces and Add-On Tools
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 30
April 20, 2004

print this article



MySQL supports small, embedded kiosk-style applications as well as the occasional five billion-record data warehouse. This versatility is possible in part because of the MySQL engine, which has been designed for maximum scalability, maximum resource efficiency, and easy portability to various platforms and architectures. This section will discuss the important characteristics of this engine in greater detail.

MySQL is designed on the assumption that the vast majority of its applications will be running on a TCP/IP (Transmission Control Protocol/Internet Protocol) network. This is a fairly good assumption, given that TCP/IP is not only highly robust and secure, but is also common to UNIX, Windows, OS/2, and almost any other serious operating system you’ll likely encounter. When the client and the server are on the same UNIX machine, MySQL uses TCP/IP with UNIX sockets, which operate in the UNIX domain; that is, they are generally used between processes on the same UNIX system (as opposed to Internet sockets, which operate between networks).

That being said, MySQL does allow named-pipe connections, which were designed mainly to support network connections in earlier non-TCP/IP networks, such as LAN Manager and Windows NETBEUI. (NETBEUI uses an addressing scheme based on the NETBIOS machine name rather than a routable IP address.)

The Structured Query Language (SQL) is an open standard that has been maintained by the American National Standards Institute (ANSI) since 1986. Although it’s true that the implementation of this standard does differ in varying degrees from vendor to vendor, it’s fair to say that SQL is today one of the most widely used cross-vendor languages. As with other implementations, such as SQL Server’s T-SQL (Transact-SQL) and Oracle’s SQL, MySQL has its own variations of the SQL standard that add power beyond what is available within the standard. Although MySQL’s SQL queries will be explored in much greater detail in subsequent chapters, we’ll introduce their use in this section to provide a framework for your understanding of the data-retrieval and decision-support capabilities of MySQL.

One thing that can be said about SQL is that it’s easy to get started. With a simple statement like this one, you can begin retrieving data:

mysqlSELECT yearmakemodelprice FROM vehiclesales LIMIT 10;
year make            model               price |
1982 AMERICAN MOTORS EAGLE 50-4WD-6 CYL. | 575   |
1983 BUICK           CENTURY LIMITED-V6  1000  |
1983 BUICK           REGAL LIMITED       525   |
1983 BUICK           REGAL LIMITED       1250  |
1983 BUICK           REGAL LIMITED       2250  |
1983 BUICK           REGAL LIMITED       700   |
1983 BUICK           SKYLARK LIMITED-V6  500   |
1983 BUICK           LESABRE CUSTOM-V8   550   |
1983 BUICK           ELECTRA LIMITED-V8  500   |
1984 BUICK           CENTURY CUSTOM-V6   850   |
10 rows in set 
(0.00 sec)

As you can see, the syntax isn’t arcane, nor is it difficult to understand—in fact, it looks almost like spoken English (which is great for English speakers). If you wanted to sort the above result set by price, you would simply add the phrase ORDER BY price to the end of the statement and run it.

Compared to C++, Java, and Perl, SQL at first glance seems easy to learn and hard to obfuscate, which often makes newcomers to SQL think that the learning curve will be much shorter than it actually is. In reality, it can take just as long to master SQL as it does other languages—people have written entire books about using SQL for data extraction without even addressing the other data manipulation features that the language offers. Take a look at the following query, which is perhaps more representative of the type you’ll be called upon to perform:

(a.price) AS s_price,
(b.price) AS a_price
FROM vehiclesales a INNER JOIN vehiclesales b
.year = (b.year 1and
.make b.make and
.model b.model and
.trim b.trim
(a.price) > (AVG(b.price)* 1.25);

This query retrieves the names of vehicles that have an average price that has gone over 25 percent of the average price of the same type of vehicle for a previous year.

Data Integrity
MySQL supports engine-level data integrity through the use of primary key and foreign key constraints. Columns can be defined so that explicit NULL values cannot be entered into them. To prevent empty columns, MySQL supports the use of default values, which, when combined with NOT NULL properties, ensure that valid data is entered into a column that would otherwise be left blank.

Until recently, MySQL was not known for its transaction-handling capabilities; however, since version 3.23, MySQL has been providing table handlers, such as InnoDB and BDB, that manage transactions in much the same manner as other commercial RDBMS products. A transaction-safe database system must pass what is known as the ACID test to qualify for compliance. An ACID-compliant database must support the following characteristics:

  • Atomicity
  • Consistency
  • Isolation
  • Durability

Let’s look at these characteristics in detail.

Coming Soon...
While future releases of MySQL are expected to have foreign keys in all table types, these referential integrity declarations are currently available only with the InnoDB table type.

Remember: this is chapter two of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed. 
Buy this book now.

>>> More MySQL Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: