The MySQL server - MySQL

MySQL has most of the features of high-end commercial database servers, including the ability to manage very large quantities of data. Its design is ideally suited to managing databases that are typical of most web database applications. The current version at the time of writing is MySQL 4.1.

The difference between MySQL and high-end commercial servers is that MySQL’s components aren’t as mature. For example, MySQL’s query evaluator doesn’t always develop a fast plan to evaluate complex queries. It also doesn’t support all of the features you might find in other servers: for example, views, triggers, and stored procedures are planned for future versions. There are other, more minor limitations that don’t typically affect web development. However, even users who need these features often choose MySQL because it’s free. (Contrary to popular belief, since 2002, MySQL has supported nested queries, transactions, and row (or record) locking.)

MySQL is another major topic of this book. It’s introduced in Chapter 5, and used extensively in examples in Chapters 6 through 8 and 11 and 12. Advanced MySQL features are a subject of Chapter 15. An example application that uses PHP and MySQL is the subject of Chapters 16 through 20. Appendixes A through C shows how to install MySQL and selected MySQL resources are listed in Appendix G.

A technical explanation of the features of MySQL 4 is presented in the next section. If you aren’t familiar with MySQL, skip ahead to the next section.

MySQL 4 is a major new release that includes important features that have been added since MySQL 3.23. The current version, MySQL 4.1, supports a wide range of SQL queries, including joins, multi-table updates and deletes, and nested queries. At present it supports most features of the SQL 92 standard, and its aim is to fully support SQL 99.

The MySQL server supports several table types that allow a wide range of choice in your applications of locking techniques, transaction environments, and performance choices. It also has good tools for backup and recovery. MySQL is a powerful, fully-featured DBMS that’s commercially supported by the company MySQL AB.

In detail, the following are the major features of MySQL 4. Many of these features are explained in detail elsewhere in this book:

Nested query and derived table support

Sub-queries are new in MySQL 4.1. This allows you to use the SQL statementsEXISTS,IN,NOT EXISTS, andNOT IN, and it also allows you to include a nested query in theFROMclause that creates a derived table.UNIONwas introduced in MySQL 4.0. All of these are discussed in detail in Chapter 15.

Internationalization

MySQL 4.1 now supports Unicode, allowing you to develop applications that don’t use Western languages. We don’t discuss MySQL’s use of Unicode in this book, but we do discuss PHP’s Unicode support in Chapter 3.

Query caching

MySQL 4.0 introduced a query cache that stores the most-recent results of queries, and intelligently delivers these as answers to identical future queries. We show you how to use this feature in Chapter 15. We explain other speed improvements in the same chapter.

Transaction-safe InnoDB tables

The InnoDB table type was included as a built-in module in MySQL 4.0. InnoDB supports transactions, and allows you to decide whether to commit or rollback a set of writes to the database. It also supports checkpointing, which is used by MySQL to get the database into a known state after a crash or serious error. We explain the advantages and disadvantages of InnoDB in Chapter 15.

Full text searching

MySQL 4 introduced new methods for fast searching of text and a form of search engine-like ranking. We don’t discuss this in the book.

MySQL 4 resources are listed in Appendix G.