The MySQL Engine - MySQL

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.

Connectivity
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.)

SQL
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:


mysql> SELECT year, make, model, price 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:


mysql> SELECT
a.year,
a.make,
a.model,
a.trim,
AVG(a.price) AS s_price,
AVG(b.price) AS a_price
FROM vehiclesales a INNER JOIN vehiclesales b
ON
a.year = (b.year + 1) and
a.make = b.make and
a.model = b.model and
a.trim = b.trim
GROUP BY
a.year,
a.make,
a.model,
a.trim,
b.year,
b.make,
b.model,
b.trim
HAVING AVG(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.

Transactions
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.

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.