Slashdot Mirror
MySQL Database Design and Optimization
This book focuses on MySQL 4.0/4.1 but also gives consideration to v.3.23 users as well as a nod toward v.5. The layout of each chapter gives a description of the topic of the chapter, followed by the meat of the chapter, a summary and what's next (how the context of this chapter ties into the subject of the next). There are numerous "notes", cautionary flags, tips, screen shots, code examples as well as thoughts from each author that provide explanatory asides to the content. The authors also provide references to other volumes, as needed.
A glance through the table of contents will give the reader a precise overview of what to expect in this book: Review of MySQL Basics; MySQL Column and Table Types; Keys, Indexes and Normalization; Optimizing Queries With Operators, Branching and Functions; Joins, Temporary Tables and Transactions; Finding the Bottlenecks, MySQL Programming; and Looking Ahead.
Chapter 1: Review of MySQL Basics gives a very quick (under 50 pages) summary of how to connect to the MySQL server; MySQL's identifiers and naming conventions for databases, tables and columns; a review of MySQL's syntax, writing basic queries and using basic commands (create, drop, select, insert, update, delete); and a discussion of the use of table, column and expression aliases. This section, while adequate, is clearly intended as an analysis of core information necessary to proceed to further chapters.
Chapter 2 follows with MySQL Column and Table Types, which deal with datatypes and structures used to store the data. The goal here is to help the reader design effective tables (and therefore create a well-designed and efficient database) suited to the particular type of data at hand. Numeric types are covered in depth; strings, the null value, ENUM and SET are also addressed as well as common "gotchas" and developer errors.
Keys, Indexes and Normalization come naturally in Chapter 3, with optimal data handling the goal: the chapter addresses getting data in efficiently and getting the results out efficiently, eliminating redundant data, appropriate uses of indexes and common index creation errors.
The core of the book is clearly Chapter 4, "Optimizing Queries with Operators, Branching, and Functions." Here, optimization skills are honed; manipulation and filtering of data is one of MySQL's strengths and this chapter shows the reader how to replace less-than-ideal program logic with SQL constructs to precisely adjust query performance. There's a good demonstration here of outputting a list of member data to a web page. The ultimate goal in this chapter is to provide the reader good skills that translate into better efficiency and faster database interaction. As the authors point out, one obvious logical consequence of this is easier migration between platforms and programming languages.
The next reasonable step is to look at additional features that MySQL has up its sleeve that will save the developer time and effort in the overall scheme of application development. Chapter 5, "Joins, Temporary Tables, and Transactions" discusses three of these additional features. The authors carefully point out that each of these eliminate excess queries needed to pull data, decrease code overhead, minimize the need to store data as application logic, decrease the number of bugs that appear in code and help guarantee data integrity (an aspect of database design that unfortunately often takes a back seat to other priorities as developers are often not concerned with the validity of data in a real world sense; i.e. from the user's perspective).
Chapter 6, "Finding the Bottlenecks," addresses modifying system configuration variables outside of the default and how these can dramatically affect performance. The authors look at some available free tools that help monitor server performance and enable configuration changes including mytop, WinMySqlAdmin, phpMyAdmin and the new MySQL Administrator (available from MySQL AB). MySQL caching capabilities and the ability to decrease repetitious read/writes to disk (good table, key and query caching within MySQL) are discussed. Finally, database interoperability and abstraction layers are mentioned in terms of performance penalties vs. making life easier for the programmer.
MySQL Programming is the topic of Chapter 7, where a very good discussion of the MySQL API is provided. There are a lot of useful examples in this chapter covering many of the common MySQL APIs available (PHP's MySQL and MySQLi, Pythons's MySQLdb, ODBC, Perl's DBI), along with feature discussions and examples.
The final chapter, "Looking Ahead," examines MySQL v.4.1, 5.0 and 5.1 and some eagerly awaited new features, including stored procedures, stored functions, views and triggers.
This is a well-rounded volume on MySQL design. There are excellent examples and the flow of the text is conversational without being rambling and unstructured. The authors have obviously taken great pains to minimize tangents and extraneous information; pithy, but with sufficient detail in mind. The reader is left with neither the sense of being overwhelmed nor longing for an explanation for a glossed-over topic. This book is pretty much a "must have" for a MySQL programmer looking to bridge the gap between novice and professional.
You can purchase Beginning MySQL Database Design and Optimization from bn.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.
nono. you're right.
MySQL is there cause people aren't willing to learn the full potential of SQL which postgresql takes advantage of.
In general, people try MySQL because
1) It's popular among php boards and its name is reknown
2) It's easy
Then you see those sites with mysql query errors when under heavy load or whichever silly programming mistakes.
MySQL is popular because its easy
And as an aside, I worked at a biotech. We had an 80 gig database with a quarter billion entries. We were running platform neutral SQL and used MySql, Oracle and MS-Sql server.
We ditched the "commercial grade" databases because they were to slow! MySql did a great job under enormous load.
You may be right there. I've heard good things about MySQL and smaller databases. Oracle, at least, is not really designed for small databases as well as it is for large ones, I think. All of the overhead doesn't really begin to provide value until you're using a good bit of data.
I don't respond to AC's.
I could look this up, but you could to, so I won't. ;-) Iirc, postgres was still randomly deleting data when mysql was feature-bare but stable. It got adopted. postgres has been slowly climbing in adoption ever since, but still lagging.
If I'm writing a php app for some guy who's going to put it on a vhosted box, they're likely going to have MSSQL *shudder* or mysql. If I'm just picking up php on a need-to-know basis, I'm not going to bother even setting up postgres in my test suite at home; and the vicious cycle continues.
That said, mysql is a very savvy company, but they seem to not be resting on the early adoption - they are constantly putting out a new, better project, and I can only imagine what kind of benefits their paid service could provide to the right project. mysql is no vhs-cabal. Betamax got buried under the brooklyn bridge.
----
Not to be confused with Col.
Yes, subqueries make the programmer's life a LOT easier. But IMHO using joins works in most cases. There's a little problem, it requires a perfect understanding of how they work, or you'd get in trouble. Also, I've read that joins is generally more efficient than doing subqueries.
Oh yes, I'm using MySQL 4.0x and it has unions, too.
If everything else fails, use temporary tables.
yo dadda sucks my ass
Chernobyl 'not a wildlife haven' - BBC News