Slashdot Mirror
Pro MySQL
Rob Lanphier writes "I'm sure there are plenty of people like myself who do a fair amount of
programming against MySQL databases, and consider it a feature of the
product that it's pretty easy to do without having to fuss much with
the actual database. Still, it's nice to look at what goes on under the
hood, even if smoke isn't pouring out from beneath it. Pro MySQL
by Michael Kruckenberg and Jay Pipes provides a broad
well-organized exploration of intermediate and advanced MySQL topics
that is a satisfying overview of the database management system." Read the rest of Rob's review.
Pro MySQL
author
Michael Kruckenberg and Jay Pipes
pages
734
publisher
Apress
rating
9
reviewer
Rob Lanphier
ISBN
1-59059-505-X
summary
MySQL overview for more advanced users
This book seems ideally suited for a couple of different audiences: Application developers with basic SQL knowledge hoping to gain a deeper understanding of the product and databases in general. The book provides a basic overview of the use of triggers, stored procedures, and cursors, among other things, before getting into the MySQL specifics. Experts in other database management systems (e.g. Oracle, Sybase) who want to apply their expertise to MySQL. The book provides a good tour of advanced MySQL functionality, and does a fair job of pointing out MySQL's shortcomings where they exist (which are much fewer in the 5.0 series). Such readers may be annoyed by the MySQL-centric views that surface from time-to-time, but all of the information should be there.
Pro MySQL is probably too advanced for someone just getting started with SQL databases in general. If you really haven't done much with SQL or relational databases at all, you should start elsewhere.
That said, there's a lot of information in here for beginners, and doesn't rush headlong into complicated discussions without at least a cursory explanation of the basics. For example, Chapter 2 has a nice review of many basic computer science concepts. Chapter 4 ("MySQL System Architecture") gives a light overview of the MySQL source code, highlighting a unique advantage of using open source. Though not the strongest chapter in the book, it includes an interesting section titled "A Typical Query Execution", which walks down the call stack explaining the source code for a typical SELECT statement. Readers might want to skip to this part of the chapter before reading the rest.
The real strengths of this book are in the chapters that deal with the database at a slightly higher level, which is most of the book. ACID compliance is covered in a thorough way, with many hands-on examples to ensure a solid understanding of the topic. The detailed discussion of the various storage engine types (focusing mostly on MyISAM and InnoDB, but touching on the others) is very useful in understanding MySQL's unique pluggable engine model, and how to leverage it. Chapters 9 through 13 provide great reference material for MySQL 5.0's advanced functionality, which includes information on stored procedures, functions, cursors, views and triggers. The final chapters (14 through 21) would stand alone as a separate book for administering a production MySQL database.
Chapter 8 ("SQL Scenarios") alone makes this book well worth the purchase price, providing recipes for solving some common problems with SQL. Some are rather simple, such as an optimization for using "OR" in "SELECT" statements in older versions of MySQL. Others are rather complicated and involved (but very useful), such as a nice way of storing hierarchical data in a table using the nested set model. The authors are careful to credit their source, Joe Celko's book Trees and Hierarchies in SQL for Smarties, for the latter. Hardcore SQL junkies may want to go straight to the source, but it's nice to see the material presented in a way that helps bring beginners up to an expert level.
The book's main shortcomings are in dealing with products that aren't downloaded off of mysql.com. MySQL's GUI tools get ample treatment (with copious screenshots, including a confirmation dialog) but the very popular phpMyAdmin tool is not mentioned anywhere. In discussing MySQL's lack of role-based access control, the authors write: "If you feel MySQL's normal user access and privilege verification system will not meet the needs of your organization, head over to [MySQL's MaxDB site] to check out how MaxDB implements its role-based system[...]", ignoring the elephant in the room, so to speak.
On a few occasions, the authors do veer into unsupported yet strongly worded assertions. For example, "This fact [that MyISAM is seemingly more efficient at storing data than InnoDB] should not be a factor in how you choose your storage engines..." (emphasis theirs). Without guidance on how much of a difference (10%? 500%? wildly unpredictable?) and a definition of the problem being solved, it's hard to say with such certainty whether or not this is a reasonable assertion. Also, "be conservative, especially when deciding on the length of character columns that will be frequently indexed", ignoring the tradeoffs that lead to the Y2K problem, for example. The advice given may be quite sound, but it's not presented in a way that convinces the reader that the authors have thought through all of the implications of their advice.
By and large, though, this is a very happy addition to my bookshelf. The organization is coherent and the chapters all have appropriate depth and have very useful information. I imagine I will refer back to this book often in building SQL applications, and I recommend it to anyone looking to boost their MySQL knowledge.
You can purchase Pro MySQL from bn.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.
This book seems ideally suited for a couple of different audiences: Application developers with basic SQL knowledge hoping to gain a deeper understanding of the product and databases in general. The book provides a basic overview of the use of triggers, stored procedures, and cursors, among other things, before getting into the MySQL specifics. Experts in other database management systems (e.g. Oracle, Sybase) who want to apply their expertise to MySQL. The book provides a good tour of advanced MySQL functionality, and does a fair job of pointing out MySQL's shortcomings where they exist (which are much fewer in the 5.0 series). Such readers may be annoyed by the MySQL-centric views that surface from time-to-time, but all of the information should be there.
Pro MySQL is probably too advanced for someone just getting started with SQL databases in general. If you really haven't done much with SQL or relational databases at all, you should start elsewhere.
That said, there's a lot of information in here for beginners, and doesn't rush headlong into complicated discussions without at least a cursory explanation of the basics. For example, Chapter 2 has a nice review of many basic computer science concepts. Chapter 4 ("MySQL System Architecture") gives a light overview of the MySQL source code, highlighting a unique advantage of using open source. Though not the strongest chapter in the book, it includes an interesting section titled "A Typical Query Execution", which walks down the call stack explaining the source code for a typical SELECT statement. Readers might want to skip to this part of the chapter before reading the rest.
The real strengths of this book are in the chapters that deal with the database at a slightly higher level, which is most of the book. ACID compliance is covered in a thorough way, with many hands-on examples to ensure a solid understanding of the topic. The detailed discussion of the various storage engine types (focusing mostly on MyISAM and InnoDB, but touching on the others) is very useful in understanding MySQL's unique pluggable engine model, and how to leverage it. Chapters 9 through 13 provide great reference material for MySQL 5.0's advanced functionality, which includes information on stored procedures, functions, cursors, views and triggers. The final chapters (14 through 21) would stand alone as a separate book for administering a production MySQL database.
Chapter 8 ("SQL Scenarios") alone makes this book well worth the purchase price, providing recipes for solving some common problems with SQL. Some are rather simple, such as an optimization for using "OR" in "SELECT" statements in older versions of MySQL. Others are rather complicated and involved (but very useful), such as a nice way of storing hierarchical data in a table using the nested set model. The authors are careful to credit their source, Joe Celko's book Trees and Hierarchies in SQL for Smarties, for the latter. Hardcore SQL junkies may want to go straight to the source, but it's nice to see the material presented in a way that helps bring beginners up to an expert level.
The book's main shortcomings are in dealing with products that aren't downloaded off of mysql.com. MySQL's GUI tools get ample treatment (with copious screenshots, including a confirmation dialog) but the very popular phpMyAdmin tool is not mentioned anywhere. In discussing MySQL's lack of role-based access control, the authors write: "If you feel MySQL's normal user access and privilege verification system will not meet the needs of your organization, head over to [MySQL's MaxDB site] to check out how MaxDB implements its role-based system[...]", ignoring the elephant in the room, so to speak.
On a few occasions, the authors do veer into unsupported yet strongly worded assertions. For example, "This fact [that MyISAM is seemingly more efficient at storing data than InnoDB] should not be a factor in how you choose your storage engines..." (emphasis theirs). Without guidance on how much of a difference (10%? 500%? wildly unpredictable?) and a definition of the problem being solved, it's hard to say with such certainty whether or not this is a reasonable assertion. Also, "be conservative, especially when deciding on the length of character columns that will be frequently indexed", ignoring the tradeoffs that lead to the Y2K problem, for example. The advice given may be quite sound, but it's not presented in a way that convinces the reader that the authors have thought through all of the implications of their advice.
By and large, though, this is a very happy addition to my bookshelf. The organization is coherent and the chapters all have appropriate depth and have very useful information. I imagine I will refer back to this book often in building SQL applications, and I recommend it to anyone looking to boost their MySQL knowledge.
You can purchase Pro MySQL from bn.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.
Pros don't use MySQL hyuk hyuk!
Databases are my friends. I program for them, not against them...
I've had it with these motherfuckin' trolls on this motherfuckin' MySQL thread!
--I'm so big, my sig has its own sig.
-- See?
Different workloads will have way different characteristics.
.mdb files at some point).
What you can tweak on locking level (table-level, page-level, row-level, MVCC, etc) in table/schema settings in other database products you pick by chosing engines in MySQL.
You can see it in a way as setting groups of settings that will be a good fit for the workload of your environment.
Jack-of-all-trades, master-of-none concept, I guess.
MyISAM - Data warehousing/DSS/Read only archives optimized for bulk operations (big scans, flexible index formats, small disk footprint for data/index) but will not do well in a typical OLTP application because of table-level locking, bad crash durability and lack of ACID in general.
InnoDB - Typical traditional OLTP, row level locking, MVCC, Clustered PK w/ using B+tree with savepoints within transactions and other features for typical OLTP applications
Memory - No disk footprint, extremely fast - for summary tables, simulations, temporary operations (, session handling?).
NDB - Typical telcom, small rows, quite a few of them and high concurrency/throughput with automatic failover in the case of node failure. Slow for big scans and joins, but great for key lookups on single tables, but with transaction volumes and throughput you can't reach with the others for this niche usage.
Falcon - Jim Starkey's new engine with some cool new things (see this link for a presentation)
Arhive - On-insert compression of rows for dump-archive-forget style data.
You can achieve lots of those things with normal settings, but to optimize on a storage level for cecertain operations can give you quite a boost for some things.
There are some cool aspects to this when it comes to proprietary formats, though. You can create engines for reading log files so you can access log data with SQL commands (there are engines for reading CSV files, apache log files, etc - think I even saw someone working on reading MS Access
Sure, it would be cool to have this one perfect storage system doing everything, but I guess you can use file systems in operating systems as an example, why doesn't all operating systems only use EXT3, Reiser, UFS, XFS, JFS, NTFS, etc? There are pros and cons to everything, just pick whatever fits your need, just like you would pick the programming language and database that would fit your application project the best.
If your database happens to be MySQL, you can go another step to pick the engine that will perform better or have the functionality you're looking for (or scalability or whatever you want).
Maybe not an acceptable reason for you, but I hope you at least understand the concept better...
cheers,
I understand why MySQL supports so many different table engines. What I don't understand is why MySQL supports (or doesn't support) different SQL features for each engine. Why should I (as an application developer) need to know whether a table was built using MyISAM or InnoDB when I write MySQL queries? Oracle has a number of different ways to optimize tables and indexes, but all of those optimizations are transparent to the application code and queries (but not to overall application performancen at runtime). I've done application development on a dozen different relational databases over the last 20 years, and MySQL is the only database engine (since DBase, and DBase wasn't really an RDBMS) where the database didn't abstract these differences away from the developer.
To use your filesystem metaphor, all of the filesystem implementations use the same VFS API -- I don't have to recode my application because I'm using ReiserFS today, when I wrote it using ext3.
We call it art because we have names for the things we understand.