Pro MySQL (Expert's Voice in Open Source) (Paperback)

by Michael Kruckenberg (Author), Jay Pipes (Author)
Key Phrases: general query log, smack file, other storage engines, Super Smack, Action Figure, Option Description (more...)

Editorial Reviews

Product Description
Pro MySQL

Pro MySQL is the first book that exclusively covers intermediate and advanced features of MySQL, the world's most popular open source database server. Whether you are a seasoned MySQL user looking to take your skills to the next level, or you’re a database expert searching for a fast-paced introduction to MySQL's advanced features, this book is for you.

The first part of this book discusses topics related to design and development. Topics include transaction processing and indexing theory, benchmarking and profiling, and advanced coverage of storage engines and data types. The authors also delve deep into the MySQL source code and system architecture at a level you won't find anywhere else. Essential advanced SQL is also covered, with discussion of subqueries, derived tables, and joins. These concepts are demonstrated in a range of situation-specific examples—from dealing with hierarchical data to performing Geographic Information System (GIS) examples with just MySQL. This section also covers MySQL 5's new enterprise features like stored procedures, triggers, and views.

The latter part of this book devotes itself to administration topics. Topics include MySQL installation and upgrading, user administration, database backups, replication, and clustering. You'll also learn about the new data dictionary features available in MySQL 5.

About the Author
Michael Kruckenberg

Michael Kruckenberg started his career with web technologies more than 10 years ago. His first major undertaking was bringing a small mail-order company online (using MySQL). After hopping around a bit during the 1990s Internet boom and spending time in the Internet startup world, Mike put his feet down at his current gig, senior programmer at Tufts University. He is now the technical manager for the Apache/Perl/MySQL-driven Tufts University Sciences Knowledgebase (TUSK), a content repository for faculty and students. Mike likes to spend his elusive free time with his wife and kids on New England adventures, has a healthy addiction to music, and likes to dabble in the arts (photography, video, and literature).

Jay Pipes

For the past 10 years, Jay Pipes has worked with all kinds of companies, large and small, to identify the value of the information they collect and help them build software that best addresses the needs of their businesses. From e-commerce to work-order management systems, Jay has been involved in projects with both Microsoft and open-source technologies. Passionate about programming and all things technical, Jay now runs his own consulting business, based in Columbus, Ohio. When not being bothered by his two cats, two dogs, and a constantly ringing phone, you can find him, headphones pulled across his ears, happily coding away at home.

Product Details

• Paperback: 768 pages
• Publisher: Apress (May 4, 2009)
• Language: English
• ISBN-10: 159059505X
• ISBN-13: 978-1590595053
• Product Dimensions: 9.1 x 7 x 1.6 inches
• Shipping Weight: 2.6 pounds (View shipping rates and policies)
• Average Customer Review: 4.6 out of 5 stars See all reviews (8 customer reviews)
• Amazon.com Sales Rank: #351,545 in Books (See Bestsellers in Books)

Customer Reviews

Most Helpful Customer Reviews

11 of 11 people found the following review helpful:

5.0 out of 5 stars Best book to learn Advanced MYSQL, August 28, 2006

By	Frank Stepanski (PA) - See all my reviews (REAL NAME)

For people like me who have a good understanding of MYSQL, the next logical step would be this book. I already know about stored procedures, transactions and the basics of triggers, but I want to learn more about these topics in more "real-world" scenarios in how to develop larger, enterprise-wide applications. Also I want to know more about other advanced topics that I may not even know to think about. If this sounds like you as well, then read on...

The author starts in with reviewing how business requirements and the software development cycle can be part of database development. This a great chapter for people like me who would like to learn how different approaches are taken from different IT perspectives in a team environment (business analysts, application developers, and project managers) This chapter gives you a good understanding of how the other side thinks.

The next chapter focuses on the importance of proper indexing and strategies as pertaining to data storage. Because once your databases reach to the multiple gigabits of storage space, you need to very focused on how long each request takes. A few seconds here and there begin to add up and can cause serious issues if not taken into consideration early in your database design. Like me who only deals with small to an occasional medium-size database I never really had to think about this before.

The next chapter focuses on transaction processing in MYSQL which is one of the more difficult concepts for many and I am in the process now of reading it. I really like the author's explanations and examples. Each point is explained thoroughly and in an easy to read manner.

The rest of the book goes into more advanced topics (system architecture, benchmarking and profiling, security, replication and clustering) as well as giving more detail and explanation to topics that may only be briefly covered in other MYSQL books (stored procedures, functions, and views).

If you already know the basics of MYSQL and want to really advance your knowledge with "real-world" scenarios, this is the book for you.

9 of 10 people found the following review helpful:

5.0 out of 5 stars An excellent resource for advanced MySQL users, December 6, 2005

By	Michael Hillyer (Alberta, Canada) - See all my reviews (REAL NAME)

I am pleased to see a new trend emerging in the MySQL books I get to review, and that is a trend toward more advanced books that assume the reader is already familiar with how to create a table and construct a basic SELECT query. Pro MySQL by Mike Kruckenberg and Jay Pipes is a shining example of this new trend. This book cuts through the basics to provide in-depth information on not only how to use MySQL, but on how the MySQL server actually works.

One example of this is their chapter on Index Concepts. Rather than focus on the basics of how to create an index, this book gives you depth by talking about seek times, buffering, and the actual structure of things such as a B-Tree index. As a result you not only know that an index on a certain column will improve performance, you also know why.

This book is also the first I have seen that takes a chapter to really dig into the MySQL source code and illustrate some of the internal workings of the MySQL server, such as the client protocol, complete with flow charts and diagrams. The System Architecture chapter is a great place to start if you are looking to understand the server internals or modify the MySQL source code.

There is a nice SQL Scenarios chapter that covers some common use cases for things such as finding duplicate and orphan records, retrieving random rows (properly), working with GIS data, and (my favorite) working with hierarchical data. The authors cover the Nested Set model nicely (which I have previously written and presented about), covering the basics of using nested sets. This provides a good starting point from which one could easily jump the Celko's Trees and Hierarchies book.

The book carries on with a good introduction to stored procedures, functions, cursors, views, and triggers to round out the first half of the book, and then spends the second half of the book focusing on administrative tasks.

There is quick coverage of installing and configuring MySQL, with more emphasis given to configuring MySQL than installing it, as is appropriate to a book on advanced MySQL usage. Following installation and configuration comes coverage of user administration, where once again flow charts are used effectively to show the authentication process within the MySQL server.

I was pleased to see the MySQL GUI tools being covered in print, as user administration was shown both using the command-line mysql client and MySQL Administrator. It was good to see the authors recommend that administrators think in terms of user roles, even if roles are not yet implemented in MySQL (the authors suggest the use of scripts to grant and revoke roles, a good workaround in the interim).

This book will certainly be noted for being one of the first to print with coverage of MySQL Cluster, as well as a full chapter on the new INFORMATION_SCHEMA, or MySQL Data Dictionary, as the authors chose to name the chapter.

All in all this is a really solid book for the experienced MySQL user who wants to go futher, and certainly lives up to its name.

6 of 6 people found the following review helpful:

5.0 out of 5 stars Moving your knowledge level up to the next level, September 17, 2005

By	Harold McFarland (Florida) - See all my reviews (TOP 50 REVIEWER)    (REAL NAME)

Written for those who already have some familiarity with MySQL and want a deeper understanding of the database system the authors take them on a detailed tour of MySQL Server version 5.0. They include many techniques that are not found in most other texts and that by itself makes this a valuable book. The authors do make the assumption that the reader already has some knowledge of relational databases and how they should work.

The book starts by examining business requirements and how software is developed from an initial requirements assessment through object and data modeling, diagramming, and completing the design of the database. The second chapter details indexing and how to use it efficiently to increase data lookup speed. The third chapter provides a detailed discussion of transaction processing. These three chapters form the foundation on which the deeper discussions in later chapters are all based.

One of the areas that I have not found discussed in any significant detail in other books is the internal system architecture of MySQL. In chapters 4 and 5 the authors examine the internal structure and the different storage engines and data types MySQL uses with a concentration on the InnoDB and MyISAM storage engines. The rest of the book covers more traditional fare such as subqueries and derived tables, benchmarking and profiling, writing SQL queries that are efficient and produce the information you want, stored procedures, stored functions, views, and triggers.

Of course they go over installation and configuration in detail as well as security, backup and restoration, replication, clustering, and troubleshooting. The authors have included lots of illustrations, tables, and examples that really help make complex MySQL concepts easy to understand. Pro MySQL is highly recommended for readers who already have a good understanding of MySQL past the beginners level and into the intermediate level.