Follow Slashdot blog updates by subscribing to our blog RSS feed

 


Forgot your password?
Close
typodupeerror
×
Databases Books Media Programming Software Data Storage Book Reviews IT

MySQL Database Design and Optimization 233

Posted by timothy from the stick-this-in-there dept.
norburym (Mary Norbury-Glaser) writes "As the title suggests, Beginning MySQL Database Design and Optimization is intended for the range of users between novice and professional. It may seem difficult for one book to suit such a wide readership without losing readers on either end of the spectrum, or perhaps without providing adequate coverage to any particular audience, Apress has done what many other publishers have failed to do by providing an excellent series of 'novice to professional' books. An example of their dedication to detail and perfection is the inclusion of top-notch technical reviewers (Mike Hillyer, in this case, often found haunting Experts Exchange as one of the top MySQL experts) who provide expertise to the series. Authors Jon Stephens and Chad Russell have extensive combined PHP and MySQL experience that shows in the content of this volume. Readers with some MySQL experience who desire a broader range of instruction will gain much from this book. Experienced users will find quite a lot of valuable information that will extend their existing knowledge base. Concepts in design are better learned from the beginning to avoid repeating poor programming mistakes, but it's never too late to learn good practices." Read on for the rest of Norbury-Glaser's review.
Beginning MySQL Database Design and Optimization
author Jon Stephens and Chad Russell (Technical Reviewer: Mike Hillyer)
pages 520
publisher Apress
rating 8
reviewer Mary Norbury-Glaser
ISBN 1590593324
summary 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.
This discussion has been archived. No new comments can be posted.

MySQL Database Design and Optimization More

MySQL Database Design and Optimization

Comments Filter:

  • For DB-neutral SQL tuning... (Score:5, Informative)

    by tcopeland ( 32225 ) * <tom@NoSPaM.thomasleecopeland.com> on Thursday December 02, 2004 @05:18PM (#10979513) Homepage
    ...SQL Performance Tuning [ocelot.ca] is an excellent book. It has a lot of good discussion on when to use certain SQL contructs and how to check your database to ensure you're actually getting improvements.

    PLUG: Which SQL queries are taking the most time? PQA home page [postgresql.org], download [pgfoundry.org].

  • common gotchas (Score:4, Informative)

    by RelliK ( 4466 ) on Thursday December 02, 2004 @05:26PM (#10979584)
    right here [sql-info.de]

    • Re:common gotchas (Score:2, Insightful)

      by ajs ( 35943 )
      That is NOT a list of "common gotchas" (the misleading name of the page aside), it's an anti-MySQL rant that has lived on far too long.

      Please, can we for once have a post about a piece of software on Slashdot without the pro-X or anti-Y folks flocking to it to bash or praise it? Can we just for once talk about the damn book?

      • Re:common gotchas (Score:2, Funny)

        by kfg ( 145172 )
        Can we just for once talk about the damn book?

        You're ne. . ., well, no, I see you're not.

        In that case the only question I'm left with is:

        Come on, you know better than that, don't you?

        KFG

      • uhhh, what? (Score:5, Insightful)

        by RelliK ( 4466 ) on Thursday December 02, 2004 @06:02PM (#10979940)
        How is this a rant? Are you saying the problems they list don't exist?

        I like that site cause it contains no spin: it just lists the facts and provides references to the documentation. Is it the facts that bother you?
        • "How is this a rant? Are you saying the problems they list don't exist?"

          Not at all. What I'm saying is that it's a page that was meant to discredit MySQL by putting together a sort of reverse-FAQ. Instead of saying, "people run into X, well here's what they do when they run into that," it's just a diatribe about how lacking someone thinks MySQL is. That, as they say, ain't news, and it most CERTAINLY does not deserve a fresh link from every MySQL article to hit Slashdot.

          • Re:uhhh, what? (Score:3, Interesting)

            by Bob Uhl ( 30977 )
            ...it most CERTAINLY does not deserve a fresh link from every MySQL article to hit Slashdot.

            I'm not so certain. A lot of folks think that MySQL is a good idea; it seems to me that it is in almost every case a mistake, and so posting such things helps ensure that this is well-known. Much like linking to GNU/Linux resources when an article concerns Windows.

            FWIW, I've used both PostgreSQL and MySQL.

            • Re:uhhh, what? (Score:2, Insightful)

              by ajs ( 35943 )
              FWIW, I've used both PostgreSQL and MySQL.

              This is a bit like disclaiming your political views by saying you've voted constitution party AND green party.

              A lot of folks think that MySQL is a good idea

              It's software that does what you tell it to do. Thus, it is a good idea. You think software product X is beter, meets some criteria that you find compelling, can do things that you want... great, that's fine, but like I said, that opinion doesn't need to be spattered all over every occurance of software pro

              • Re:uhhh, what? (Score:3, Insightful)

                by kpharmer ( 452893 ) *
                > It's software that does what you tell it to do.

                Actually, no.

                The point of the many of the 'gotchas' is that this software behaves erractically: rather than produce an exception during an overflow or conversion error, for example, it just silently modifies the data and returns no warning to the user.

                The truly bizarre thing about this set of errors is that it is about the only database management software you'll find that is so guilty of this behavior. You'd never accept that behavior from SQL Server,
                • "The point of the many of the 'gotchas' is that this software behaves erractically: rather than produce an exception during an overflow or conversion error, for example, it just silently modifies the data and returns no warning to the user."

                  A well documented convinience for applications which always behaves the same way.

                  Please explain your definition of erratic for me, because mine doesn't seem to match the example you give.

                  "Two years ago the folks from MySQL stated that transactions, views, subselects
                  • From M-W online:

                    3a : characterized by lack of consistency, regularity, or uniformity b : deviating from what is ordinary or standard

                    MySQL's behavior is erratic in that it is e.g. incredibly incosisitent when it comes to NULL-related behavior. Just admit it and move on with your life, you'll be a happier person.

                    It's just that you don't need to carpet-bomb every article about products you don't care for.

                    One post hardly consitutes carpet bombing. In fact, the only single person I see prolonging th

                    • One post hardly consitutes carpet bombing.

                      I was refering to the fact that this link shows up in EVERY MySQL related article, within minutes. That, I call carpet bombing. Use your own terminology as you see fit.

                      In fact, the only single person I see prolonging this thread (by responing to these so-called "anti-MySQL" people as you like to label them) is you.

                      One, I've never refered to anyone in this thread as an anti-MySQL person. Not a one. Please, don't re-write my comments.

                      Two, you are right: I've pu
                    • One, I've never refered to anyone in this thread as an anti-MySQL person. Not a one. Please, don't re-write my comments

                      Must've been somebody else then. Sorry about that.

                      Oh, and I marked you a foe because you (IMO, of course) acted like an ass throughout the thread.

                  • Re:uhhh, what? (Score:3, Insightful)

                    by kpharmer ( 452893 ) *
                    > A well documented convinience for applications
                    > which always behaves the same way.

                    Pardon? So when MySQL fails to report of an exception (incorrect date, string overflow, etc, etc, etc) - that's intentional? It wasn't sloppiness or incompetence? So, should the other database vendors start eliminating exception handling as well - perhaps in the interest of keeping the product easy to use?

                    On the other hand, maybe you need to get a little emotional distance from the product.

                    > Please explain you

          • Not at all. What I'm saying is that it's a page that was meant to discredit MySQL by putting together a sort of reverse-FAQ.
            Given the author has a page for PostgreSQL as well (albeit a short one), I'd suggest you're full of shit.

            it's just a diatribe about how lacking someone thinks MySQL is.
            Since when is pointing out things that a piece of software does incorrectly, especially when it claims to do them correctly and noting the relevant examples and documentation to do that, a diatribe?

            That, as they

            • people think twice about using MySQL

              What is this... I'm really freaking confused. MySQL is used by tens if not hundreds of thousands of people around the world. It has always done what I want it to do faithfully. I'm a Sybase weenie with significant Oracle and Ingres experience. I've been working with and writing software for 15 years and I honestly do not understand what is so damned scary about MySQL. Send query, get answer. Life good. Carry on.

              If it doesn't do what you want, fine don't use it, but ple

          • Instead of saying, "people run into X, well here's what they do when they run into that," it's just a diatribe about how lacking someone thinks MySQL is.

            The trouble is that the MySQL documentation make some very contentious statements in an attempt to justify the lack of features. Saying things like "you don't need foreign keys because ..." and then presenting some hideous hack that the developer has to do in code because MySQL has such a piss poor feature set. Yup, I know version 4 in some configuratio

        • Are you saying the problems they list don't exist?
          Oh, I'm sure problems exist. They are in the nature of What is the value of something that does not exist? Different choices can be made and it is unreasonable to expect the choices that MySQL has made will exactly match the choices that you would make. What is the "value" of 0/0? A value does exist, it's just not numeric. Calling it undefined does not prevent it from happening.

          What is completely missing is any indication of practical use of the distinctio
      • They're not saying that these are problems - they're gotchas! They are behaviors that you would not normally expect. Once you understand them, you can work with them, but before then, they will not be what you expect.

  • MySQL and PostgreSQL RFD in news.groups (Score:5, Informative)

    by Anonymous Coward on Thursday December 02, 2004 @05:39PM (#10979698)
    The RFDs (Request for Discussions) for both PostgreSQL and MySQL are on news.group. In about one month, both groups will be voted on, if it passes, the groups will be found under comp.databases.*.

    If you want more information, visit news.groups with your usenet server.

    Right now, there aren't ANY postgresql or mysql groups under the big 8 comp. domain.

    Remember to stay tuned for the CFV so they get voted into the domain! Here is a nice web poll you can take to voice your support of the groups getting into the big 8 usenet hierarchy:

    http://scripts.postgresql.org/survey.php?View=1& Su rveyID=36

    Vote yes, so they know there is support for a big 8 comp.databases.postgresql newsgroup as one does not exist yet!
    • I just checked it out on my usenet server, and it is true. There are NO MySQL and Postgresql groups in the comp.databases.* hierarchy on usenet.

      I would recommend anyone who uses these databases to stay tuned to news.groups and find out how to vote for the creation of these groups on usenet. The result would be the creation of the following groups:

      comp.databases.mysql
      comp.databases.postgresql

  • UK People (Score:4, Informative)

    by RobertTaylor ( 444958 ) <roberttaylor1234.gmail@com> on Thursday December 02, 2004 @05:42PM (#10979730) Homepage Journal
    If you are in the UK you can get the book here [greatdeal.co.uk] for 10% off and free delivery up to Dec 25th :)

    A few are floating around for £20 as well.

  • MySQL for beginners? (Score:4, Interesting)

    by owlstead ( 636356 ) on Thursday December 02, 2004 @05:54PM (#10979858)
    Don't you want to start with just database design and SQL before you would want to move to a book about a specific RDBC implementation? If it is just about database design then the title of the book is wrong.

    Then again, if you wish to explain about setting up the database itself, access rights and so on, then the book might be for beginners. Once again, the title would not fit the book.

    As anyone should know, the steps in software development are: get it working, get it right, get it optimized. Let's hope that the book does not go to deep into the optimized part in a too early stage.
  • I've been doing the PHP/SQL thing for money for a while now, and I've been able to meet all my needs so far. I've taken a peek at Postgres, but I haven't found a good explanation and usage examples for some of the features that mysql lacks. I understand nested queries, transactions, and foreign keys, however I haven't found anything that helps me understand stored procedures, views, or triggers.
    • First, I'd like to point you to my favorite book about database theory, even though it's not exactly what you asked for:

      "An Introduction to Database Systems" by C.J. Date. It's a great book and I liked it so much I bought another by the same author: "Foundation for Future Database Systems - The Third Manifesto". Both are very theoretical and also very precise.

      On a more practical level:
      * Stored Procedures: In postgres, these are the same as functions. They have several distinct purposes:

      (1) Produce data i
  • What would be nice is a book review (and book) showing developers the best practices to PHP and Pear DB development so that PHP programmers can create apps that are SQL database agnostic -- i.e. can have a MySQL or PostgreSQL backend without much code change in PHP. A mere change in the PHP line telling it what server, where, and login will only be required. I'm sure this book is probably already written, anyone care to point me where?

    • It's not even limited to people changing RDBMS.

      Pear DB would be extremely useful even for just upgrading from mysql to mysqli, utilizing MySQL 4.1-features (such as prepared statements).

      I have used Pear DB for a couple of applications. Now, after upgrading to MySQL 4.1, I'll just have to change one item (phptype from 'mysql' to 'mysqli'), as I'm already using prepare-statements.

      Even if one don't find a switch from mysql to another system realistic for any reason, Pear DB is still useful for internal swit
    • Face it, if you want to write commercial apps you have to CHOOSE.

      I worked in a _HUGE_ database project and it was slow as hell because we couldn't use native solutions for optimization, because we were required to maintain code compatibility. They had licensed Informix, and we got to maintain it compatible (to justify a gazillion dollars investment in an already obsolete DB).

      So, want to use Limit? No thanks. Want to find out the thread ID's? No thanks. Want to improve performance by using native mySQL fun
    • If you are looking at changing databases, you want to learn standard SQL before you touch Pear DB. Pear DB is nice, but wont help you much if your SQL statements are all MySQL specific. If you want to learn a bit about PHP the OReilly book Programming PHP has a good chapter on Pear DB. It assumes you already know SQL so if youre after a primer on SQL look elsewhere, but if you want to know how Pear DB works its quite good.
    • There are some applications where it doesn't matter much what DB you use. For those it's great to be "DB agnostic".

      However, let me warn you that what often happens is that you end up going with the lowest common denominator and use no specific optimizations. That might not be so bad in the really simple cases. However, in all other cases it means that the application has to pick up the slack, and do the stuff that you would normally ask the DB to do.

      That means that your data abstraction layer becomes huge

  • Database design? (Score:3, Insightful)

    by Tablizer ( 95088 ) on Thursday December 02, 2004 @09:05PM (#10981797) Journal
    Database design should be a generic RDBMS book for the most part. It does not make much sense to repeat table design techniques and philosophy for each RDBMS product. (However, giving vendor-specific tips and limits is understandable.) It might be cheaper to purchase and write a generic book about table design because it can be written and printed for multiple products. Then again, many publishers simply copy-and-paste semi-generic topics with slight custom tuning.

  • Still needs lots of work (Score:4, Insightful)

    by gtoomey ( 528943 ) on Thursday December 02, 2004 @09:17PM (#10981901)
    I'm using 4.1 (its not a production release yet) which has subqueries, proper joins, and unions. Its the first version which is even remotely acceptable. Coding without subqueries is very frustrating

    Views, synonyms and referential integrity (foreign key constraints) would be very nice too.

    When I find out why VHS became more popular than technically superior Betamax, I'll figure out why Mysql is more popular than Postgres.

    • Use InnoDB tables instead of MyISAM tables and you can have foreign key contraints. Also, if you consider an application where data is only ever input through the web or some other front-end GUI, foreign key contrains aren't necessarily needed (I'll admit they're still good to have and use), because you can control the input through restricted UI elements like drop-downs, radio buttons, etc.

      And MySQL 4.1 HAS been certified production-ready, for what that's worth.

      Coding without subqueries is a pain though,
      • Also, if you consider an application where data is only ever input through the web or some other front-end GUI, foreign key contrains aren't necessarily needed (I'll admit they're still good to have and use), because you can control the input through restricted UI elements like drop-downs, radio buttons, etc.

        I know a lot of people do that (myself guilty on occasion), but you're the first person to publicly admit it :)

        Constraints are very important.

        First, in the app, you check the input for basic securit
    • I'm using 4.1 (its not a production release yet)...
      MySQL 4.1 series marked as stable [slashdot.org]
    • Coding without subqueries teaches you a lot about SQL.

      Seriously, before reading stuff about how to get around not having subqueries, I was writing much less efficient SQL code.

      Now, I rarely ever need subqueries even though they're available -- I've learned to optimize many of them into joins, or pre-query the information I want seperately since I'll usually reuse it several times elsewhere.
  • Hi, I'm one of the authors of the book.

    1. Thanks to Mary for the positive review.

    2. Thanks to Mike Hillyer for his invaluable help with the book. Say what you like about Visual Basic (I happen to loathe it, myself), Mike's an excellent programmer, and his knowledge of MySQL is superb. In fact, part of the way through the process of writing this book, he was hired by MySQL AB to work with the teams developing the Connectors and the new GUI tools. His site VBMySQL.com provides a valuable and unique resource for VB and other Windows developers wanting to build DB applications who'd like to use an actual database instead of Access and don't feel like condemning themselves or their users to paying for SQL Server. Rather than flame him for his language and platform choices, you should commend him for introducing many Windows programmers to an Open Source technology. (BTW, you might be interested in knowing that he also uses Linux and programs in C++ as well.) It was a privilege to have him work on the book with us, and it's a privilege to work with him now at MySQL AB. And he's a damn good writer.

    3. We wrote the book because there's a lot of MySQL installations out there, and a lot of very badly done MySQL databases. Granted, there are some things that MySQl isn't (yet). But it is fast and stable -- or can be. And it's certainly possible to throw those advantages away through poor DB and application design by people who don't know the difference between a database and a spreadsheet or who don't know how to leverage SQL to do their heavy lifting for them. We chose not to spend a great deal of time with enforcing foreign keys because a great many administrators are still running MySQL 3.23 and don't bother to make InnoDB available. Besides, if you expect people to understand key constraints, you have to get them to normalise first, and many devs don't even do that.

    4. We wanted to encourage PHP developers to make the transition to ext/mysqli as soon as possible.

    5. I don't know what other people may have experienced with Apress, but they've been damned nice to me, and I can tell you that Gary Cornell does answer his email, even when it comes from a lowly writer who's not yet even signed a contract. Speaking of which -- their contracts are much better than Wrox' or Wiley's. And since I've been associated with them, they've dumped at least one bad editor and another one that I'd heard some not-so-favourable things about.

    6. While we didn't cover this in the book, fans of Postgres might wish to take note: We already have a working Cluster implementation, and we're anxious to see what yours will look like. :)

Slashdot Top Deals

Do you suffer painful hallucination? -- Don Juan, cited by Carlos Casteneda

Close