Home arrow MySQL arrow Page 9 - Database Applications and the Web

The MySQL server - MySQL

With most of the services on the web being powered by web database applications, it becomes important for any web developer to know how bring together the web and databases to build applications. This article gets you started. It is excerpted from chapter one of the book Web Database Applications with PHP and MySQL, written by Hugh E. Williams & David Lane (O'Reilly, 2004; ISBN: 0596005431).

  1. Database Applications and the Web
  2. The Web
  3. HTTP: the Hypertext Transfer Protocol
  4. Thickening the Client in the Three-Tier Model
  5. Web Scripting with PHP
  6. Introducing PHP5
  7. The Database Tier
  8. Why use a database server?
  9. The MySQL server
By: O'Reilly Media
Rating: starstarstarstarstar / 45
September 15, 2005

print this article



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.

Introducing MySQL 4

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.


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.

>>> More MySQL Articles          >>> More By O'Reilly Media

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: