Summary

The authoritative, hands-on guide to advanced MySQL programming and administration techniques for high performance is here.MySQL Database Design and Tuningis the only guide with coverage of both the basics and advanced topics, including reliability, performance, optimization and tuning for MySQL. This clear, concise and unique source for the most reliable MySQL performance information will show you how to: Deploy the right MySQL product for your performance needs. Set up a performance management and monitoring environment using tools from MySQL. Implement the right indexing strategy Apply good performance strategy when developing software to work with the MySQL database. Configure dozens of variable to correctly tune the MySQL engine. If you deal with the intricacies and challenges of advanced MySQL functionality on a daily basis, you will be able to build on your knowledge with authorRobert Schneider'sreal-world experiences inMySQL Database Design and Tuning.

Author Biography

MySQL® Database Design and Tuning About the Author

Robert D. Schneider has more than 15 years of experience developing and delivering sophisticated software solutions worldwide. He has provided database optimization, distributed computing, and other technical expertise to a wide variety of enterprises in the financial, technology, and government sectors. Clients have included Chase Manhattan Bank, VISA, HP, SWIFT, and the governments of the United States, Brazil, and Malaysia.

He is the author of Optimizing Informix Applications and Microsoft SQL Server: Planning and Building a High Performance Database. He has also written numerous articles on technical and professional services topics. He can be reached at Robert.Schneider@Think88.com.

The Purpose of This Book
Who Should Read This Book?
Benefits of Using This Book
How to Use This Book
Information About MySQL Software and Environments
Information About Examples
Introduction
Setting Up an Optimization Environment
Pretesting Preparation
Hardware
Connectivity
Software
Data
Your Test Plan
Change One Variable at a Time
Organizational Support
After Testing Is Under Way
Posttesting Implementation
Recording Your Results
Making Improvements
Performance Monitoring Options
MySQL Tools and Utilities
Character-Based Tools and Utilities
Graphical Tools
Logging
Operating System Tools
Windows
Linux Unix
MySQL Overview and New Performance-Related Features
MySQL Products
MySQL Core Technologies
Database Storage Engines and Table Types
Distributed Computing Technologies
Graphical Tools and Assistants
Connectors
APIs
Utilities
Performance-Related Tools
About the Versions Used for This Book
MySQL Versions
Operating Systems
Performance-Boosting Features from Version 4.0 Onward
Advanced Database Design Concepts
Designing for Speed
Choosing the Right Storage Engine and Table Type
InnoDB
MyISAM
Memory
Merge
CSV
Archive
BerkeleyDB (BDB)
MaxDB
NDB
Federated
Optimizing Table Structure
Specifying Row Format
Specifying Table Size
Index Key Compression
Checksum Integrity Management
Column Types and Performance
Using Views to Boost Performance
Reduced Returned Columns
Reduced Returned Rows
Reduced Inefficient Joins
Simpler Updating with Integrity Checking
When Not to Normalize
Using Constraints to Improve Performance
UNIQUE Constraints
Primary Key
Foreign Key
DEFAULT and NOT NULL
ENUM
SET
Understanding the MySQL Optimizer
What Is an Optimizer?
Optimizer Diagnostics and Tuning
The ANALYZE TABLE Command
The OPTIMIZE TABLE Command
The EXPLAIN Command
Helping the Optimizer
Indexing Strategies
Index Key Terms and Concepts
Index Reports and Utilities
Show index
Assisting the Optimizer with Indexes
Index-Related Logging
MyISAM Indexing Features
Key Cache
The myisamchk Utility
Index and Table Compression
InnoDB Indexing Features
Index Structure and Storage
Buffer Pool
Memory Pool
Adaptive Hash Index
Automatic Foreign Key Index Generation
Indexing Scenarios
Primary key
Filter Columns
Join Columns
Index Cardinality
Character Versus Numeric Indexes
Multicolumn Indexes
Partial Indexes
Ascending Versus Descending Indexes
Storing Tables in Column-Sorted Order
Optimizing Application Code
Advanced SQL Tips
Improving Searches
Leveraging Internal Engine Caches
Controlling Data Retrieval
Reducing Security Costs
Off-loading Processing Work
Boosting Join Performance
Substring Searches
Improving Temporary Table Performance
Managing View Performance
Subqueries
Using Math Within SQL
UNIONs
Sorting
HANDLER
Impr
Table of Contents provided by Publisher. All Rights Reserved.

Supplemental Materials

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

Excerpts

= 0) {slash = '\\';} else {slash = '/';}openLoc = figLoc.substring(0, figLoc.lastIndexOf(slash) + 1);while (pPage.substring(0,3) == '../') {openLoc = openLoc.substring(0, openLoc.lastIndexOf(slash, openLoc.length - 2)+ 1);pPage = pPage.substring(3, pPage.length + 1);}popUpWin =window.open('','popWin','resizable=1,scrollbars=1,location=0,toolbar=0,width=525,height=394');figDoc = popUpWin.document;zhtm= ' ' + pPage + ' ';zhtm += ' ';zhtm += ' ';zhtm += ' ';zhtm += '' + pPage.substring(pPage.lastIndexOf('/') + 1, pPage.length) + '';zhtm += ' ';figDoc.write(zhtm);figDoc.close();}// modified 3.1.99 RWE v4.1 --> Introduction Introduction The Purpose of This BookMySQL is the foundation of many of today's highest quality, most robust database-driven solutions. However, users continually grow more demanding: A rich application that runs sluggishly is unacceptable, no matter what functionality it offers. Unfortunately, the cost of job cutbacks and outsourcing means that most overloaded IT professionals barely have enough time to deliver a minimal solution, much less master the minutiae of their database engine, which, in the case of MySQL, grows richer and more complex with each release.Performance often takes a backseat to merely hitting the schedule. "Just ship it now and make it faster later" is a refrain heard around the world today. Regrettably, "later" rarely arrives, and designers, developers, and administrators end up delivering suboptimal solutions and then bracing themselves for the inevitable complaints from users. In many cases, these problems would never have come to pass if the overloaded designers and developers had enough time to fully research and exploit the numerous avenues to boost MySQL response. This book was written to help these beleaguered professionals jump-start their knowledge of MySQL performance tuning, letting them design, develop, and maintain the fastest MySQL solutions in the shortest amount of time. Who Should Read This Book?MySQL Database Design and Tuningis aimed at busy database designers, database and system administrators, webmasters, and developers who want to build, deploy, and manage the fastest possible solutions based on MySQL.To take full advantage of this book, application developers should understand the following:Standard Structured Query Language (SQL)--Because SQL is the underpinning of MySQL database access, it's important that you at least comprehend basic SQL (simple queries, INSERT/UPDATE/DELETE operations, and so on).A programming language such as Java, C++, and so on--SQL is often paired with a procedural progr