Forgot your password?
typodupeerror

Pro MySQL 100

Posted by samzenpus
from the read-all-about-it dept.
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 discussion has been archived. No new comments can be posted.

Pro MySQL

Comments Filter:
  • by Anonymous Coward on Wednesday August 23, 2006 @03:50PM (#15964954)
    Pros don't use MySQL hyuk hyuk!
    • Re: (Score:1, Funny)

      by Anonymous Coward

      So tag the article "oxymoron" :)

      • by seek31337 (520238)
        Yeah, no serious sites use MySQL. They *ALL* use Postgres. Right? Right? It's like saying Pro Linux. Hah!
    • by GoodOmens (904827)
      Lol I'm only using Oracle because of the Goverment.

      And its Oracle 8.1 .... yuck! Give me at least 10G ~.~
  • Pro Advanced Hints (Score:3, Interesting)

    by Anonymous Coward on Wednesday August 23, 2006 @03:53PM (#15964972)
    Is there fulltext indexing with innodb tables yet? Or should I not be using FT indexes because they bog down users with all that convenience and ease-of-use?
  • by creimer (824291) on Wednesday August 23, 2006 @03:56PM (#15964995) Homepage
    Smoke usually comes out of a server when the CPU is on fire or out of the ears of someone paying Oracle fees. But I have never seen a smoking database.
    • Re: (Score:3, Funny)

      by solafide (845228)
      It happens. It happens when some Luddites are hosting their own website, about 1 hit a day, and are smoking a cigarette. Then they get slashdotted and they're still using card catalog databases, so the Luddite has to get to work serving out of the database, the cig falls out of his mouth, and the database catches on fire. :)
  • (aside from mysql.com/doc/ of course):
    Zawodny's "High Performance MySQL", from O'Reilly. Highly recommended for DBAs and sysadmins looking to design, build and operate MySQL clusters, do backups and replication, and generally squeeze the most performance they can out of MySQL (and to a lesser degree, out of LAMP in general).
    • by OnyxRaven (9906)
      I have to throw in my agreement - the High Performance MySQL book is a thing of greatness, which seems to cover all the bases for a professional using MySQL.

      One of the better things about the book is it does explain how the underlying engines store and retrieve data, which gives good insight into performance problems and how to tune databases and requests for proper and efficient execution.
  • by xxxJonBoyxxx (565205) on Wednesday August 23, 2006 @04:12PM (#15965085)
    ...MySQL's unique pluggable engine model...
    I've been working with MySQL for something like seven years. Not once has it been important that MySQL's engine model is "unique" or "pluggable"; what is important is that it supports a couple of different database formats.
    • Re: (Score:2, Informative)

      by flupps (193729)
      And it's not pluggable until 5.1, prior to then it still has to be compiled in.
    • Re: (Score:1, Offtopic)

      by aboyko (16319)
      Help me out here, because I've only really worked with PostgreSQL for the last seven years. Why is it important to support multiple database engine types, rather than settling on one good one? I'm given to believe that the general choices are between "ACID/correct/stable" and "WIKKED AWESUM FASSSST", but why this is a choice a DBA should have to make (per table? per database?) is unclear to this PostgreSQL user. Is there more to it? What keeps people from embracing the One Good One at this point?
      • by flupps (193729) on Wednesday August 23, 2006 @05:12PM (#15965555) Homepage
        Different workloads will have way different characteristics.

        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 [wikipedia.org] new engine with some cool new things (see this link [kruckenberg.com] 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 .mdb files at some point).

        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,
        • by miniver (1839) on Wednesday August 23, 2006 @06:13PM (#15965992) Homepage

          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.

          • by flupps (193729)
            Most of them are special purpose, and if you use them you design your whole application around the specific workings.

            The more general purpose ones (InnoDB, MyISAM, BDB, NDB, Falcon) are quite transparent.
            MyISAM of course doesn't fill ACID, so that would be something you definitely would want to know about, but otherwise it you should be able to switch without a single line of application code needing to change.
            (only things I can think about with syntax differences is if you use the GIS extension or fulltext
            • by miniver (1839)
              I was thinking about sub-queries, which the last time I checked, a significant part of the SQL standard, but not supported by some of the 'general purpose' table types.
              • Re: (Score:2, Informative)

                by flupps (193729)
                Subqueries are handled on a layer way above of the actual storage layer, so all engines can handle this.

            • by jadavis (473492) on Thursday August 24, 2006 @01:08AM (#15967661)
              For each "feature" of the database, the MySQL advertisers (or advocates) use a different storage engine. For example, if you want the feature "easy to use, right out of the box" you get MyISAM, which means no ACID. If you want ACID, you use InnoDB. Full-text search and you're back to MyISAM. Cluster, you need a memory database that won't survive a power failure. If you like all the apps currently written for MySQL, you can't use strict mode and you're stuck with the least compatible dialect of SQL anywhere. If you want SQL compatibility, turn on strict and you can't use those other applications. In order to tell if MySQL actually meets the needs of your application (today), you have to build a huge matrix of compatibility. I hope you don't need ACID compliance and full text indexes at once! So much for easy to use...

              I think of MySQL as tradeoff-DB. Usually it's bad tradeoffs that you don't have to make in any other database.

              In my opinion, it's a great marketing ploy. People pay attention to claims, not caveats. Shout loud that you have a feature X, Y, and Z. That will work, even if the small print says you can't use X and Y simultaneously, and Z has some huge drawback. Nobody can ever "win" an argument against MySQL because the person you're arguing with can always claim features X, and downplay the importance of Y. Then, when you point out the importance of Y, someone else claims that it supports Y, and downplays X. And then they claim Z is just an extra feature for special use, and there's no really logical way to argue against including an option. MySQL is horribly misleading in its advertising.
          • I think you're missing the mark here. Subqueries in MySQL are not dependant of the engine but of the version. MySQL3 doesn't support them, MySQL4 does, regardless of the engine.
        • by jan.blaha (851003)
          Memory - No disk footprint, extremely fast - for summary tables, simulations, temporary operations (, session handling?).
          Actually, there's an example of another bad tradeoff: memory tables use fixed record length. They have no support for text type columns. Imaginary session data would have to be handled with CHAR column.
  • by Anonymous Coward
    If you are just starting out with SQL, what book(s) would those familiar with it recommend?
    • Re: (Score:3, Interesting)

      If you are just starting out with SQL, what book(s) would those familiar with it recommend?

      If you mean MySQL, the topic at hand, I have nothing for you. However, if you meant exactly what you wrote, I picked up Sams Teach Yourself SQL in 10 Minutes [amazon.com] from my local library last fall, and loved it. Learned a lot, quickly.

    • You may want to look into one specific to your DBMS. I started writing SQL for Informix and used The Informix Handbook which covered a wide range of Informix topics related to many areas outside of SQL.

      Was it the best book for the time? Not really but the price was right and it helped introduce me to other DB concepts that would become more important the more I worked with Informix.
    • by Ruie (30480)
      If you are just starting out with SQL, what book(s) would those familiar with it recommend?

      O'Reilly pocket mysql. Or just read MYSQL docs.

      • I'll give another vote for the mysql docs [mysql.com]. I dont think I have ever had an issue with mysql that was not solved somewhere in their massive documentation archive. (ok ok until I started playing with C-JDBC ;))
      • by NineNine (235196)
        MySQL is a (low end) database product. SQL is a language. Somehow, I doubt that a book covering a certain product is appropriate for somebody wanting to learn a lanugage.
        • by Ruie (30480)

          MySQL is a (low end) database product. SQL is a language. Somehow, I doubt that a book covering a certain product is appropriate for somebody wanting to learn a lanugage.

          I actually misspelled, the book name is "pocket sql" or something like that, it describes sql syntax specific to sql server, oracle, db2 and mysql.

          As for "low end" this is very relative. I am crunching through .7 TB of data sitting in MySQL right now and it is quite efficient. In particular, if one were to construct a metric like databas

    • by Gulthek (12570)
      You could do worse than 'Learning SQL' (which does all but a couple examples in mySQL). No need to get into mySQL specifics until you know how to use SQL itself.
    • by Gospodin (547743)

      I'm sure there are many fine ones, but I started with Understanding SQL [amazon.com]. It's a bit old (1990), but there is a 2nd edition available (although only "used" on Amazon, which is why I didn't link to it). It's pretty DBMS-agnostic, covers the basic ground pretty well. I learned a lot without even having a DBMS to practice with (this was pre-MySQL).

    • Re: (Score:3, Informative)

      by Anonymous Coward
      Before learning any particular product, learn the basic theory that underlies all data management. Even just a small understanding of what a database is, how it links to facts in the real world, and what the underlying operations are will put you light years ahead of your peers.

      The best book I've found for that is "Database in Depth" by Chris Date. Very short, less than 300 pages. In fact the relational model is pretty simple, you could explain it succinctly in a page or two, but amazingly, so few people bo
      • Re: (Score:3, Interesting)

        by jadavis (473492)
        I read and enjoyed both An Introduction to Database Systems and The Third Manifesto, both also by Date. Would you say that book would add something, or would it be redundant with his other writings?
    • by flupps (193729) on Wednesday August 23, 2006 @05:40PM (#15965787) Homepage
      Those are maybe not newbie books, but the books here all have some nuggets while learning databases (maybe not the initial start, but after a while if nothing else)

      An introduction to database systems - C.J. Date [amazon.com]
      Covers the relational model, quite heavy on theory, but gives an excellent walkthrough of the fundamentals.

      SQL For Smarties - Joe Celko [amazon.com]
      A good SQL Programming book, covers normalization and most features found in databases, anyone programming databases should read this one

      SQL 1999 Complete, REALLY - P. Gulutzan, T. Pelzer [amazon.com]
      Good coverage of what's included in the 1999 SQL standard and some examples and discussions around this.

      Translucent Databases - P. Wayner [amazon.com]
      Covers how to handle sensitive data in databases, letting users use data, but not be able to fetch or abuse it

      The two MySQL books I'd recommend are "High Performance MySQL" by J.Zawodny and D.Bailing and "MySQL" by Paul Dubois.
      Another good database book for performance tuning is "SQL Performance Tuning" by P.Gulutzan/T.Pelzer

      Hope this helps...
      cheers,
  • by gandracu (951016) on Wednesday August 23, 2006 @04:17PM (#15965132)
    Databases are my friends. I program for them, not against them...
  • Is something wrong with Slashdot? A review that is *not* a glorified table of contents???
  • Me, Myself & I (Score:3, Informative)

    by d3xt3r (527989) on Wednesday August 23, 2006 @04:44PM (#15965314)
    <RANT>

    I see too many people, including the submitter, use myself where me or I should be used. Hey, you don't sound more professional when you use myself in places it doesn't belong, you actually sound like a moron.

    Here [wsu.edu] are [uwf.edu] some [writing911.com]resources [blogspot.com] for anyone still confused about this.

    </RANT>
  • Spelling? (Score:4, Funny)

    by Chief Typist (110285) on Wednesday August 23, 2006 @04:59PM (#15965442) Homepage
    Shouldn't "Pro MySQL" be spelled as "PostgreSQL"? :-)

    -ch
    • by oSand (880494)
      ef eye are ee bee eye are dee. A very good database that doesn't give the impression it has been scotch taped together by monkeys. Nice permissive licence, feature rich, reliable. firebirdsql.org
  • Great book (Score:3, Interesting)

    by rylin (688457) on Wednesday August 23, 2006 @05:04PM (#15965482)
    While at this year's MySQL Users' Conference, I managed to pick this book up at the quiz show.
    Rather than what the blurb said regarding audiences, I'd say it's geared towards developers and dbas or system administrators - not people migrating from another db vendor.

    It does show some common pitfalls; some unique to mysql, some to sql in general.
    I definitely liked the in-depth chapters regarding the way the core engine and various storage engines work, and all in all I'd have to say it's probably the best book about mysql I've read.

    A few glaring omissions are in it, but I'm sure there'll be a new revision in a few months' time.
  • I've just programmed an extensible .Net data layer that supports MySQL, OLE DB (access) and SQL server. I can switch between databases with an entry in the app.config file. I have a couple of questions: MySQL uses the ` character to delimit fields, and SQL/Access use [ and ]. There is also the "LIMIT" clause vs the "Select TOP N" for Access/SQL. Which provider is correct? Are there standards for either of these?
    • by flupps (193729)
      MySQL uses the ` delimiter, but supports the correct (ANSI) " delimiter in ANSI mode or if you run with the ANSI_QUOTES option.

      Neither LIMIT nor TOP N is correct from a standard point of view.
      SELECT c1 FROM t1 ORDER BY c1 LIMIT 10 | SELECT TOP 10 c1 FROM t1 ORDER BY c1 should according to the standard be written as:

      SELECT c1 FROM (
      SELECT
      ROW_NUMBER() OVER (ORDER BY c1 ASC) AS rownumber, c1
      FROM t1
      ) AS tmp
      WHERE rownumber = 10
    • by EsJay (879629)
      In MS SQL, the command SET QUOTED_IDENTIFIER ON (or SET QUOTED_IDENTIFIER OFF) toggles the treatment of quotes.

      From MS SQL Books Online:
      "[SET QUOTED_IDENTIFIER ON] causes Microsoft SQL Server to follow the SQL-92 rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not usually allowed by the Transact-SQL syntax rules for identifiers."
  • MySQL 5 (Score:4, Interesting)

    by larry bagina (561269) on Wednesday August 23, 2006 @05:48PM (#15965849) Journal

    Some of the "advanced" features of MySQL 5 make it more palatable to me. (I have a hard time triggers or views advanced when SQLite and every other SQL db supports them.)

    A lot of open source development web apps are targetted towards the lowest common denominator -- shared hosts that still use MySQL 4 -- so the adoption has been kind of slow. I have a couple GPL webapps which don't support MySQL due to the lack of triggers and views. Sure, you can fake it on the client side, but then again, you can fake an orgasm. I'd rather do it the proper way.

  • by Anonymous Coward on Wednesday August 23, 2006 @06:05PM (#15965951)
    We recently had to implement a custom CRM system, and as would be expected we needed the services of an SQL database. One of our project leads actually had this book on his bookshelf, and the rest of us on the team took a chance to look over it during the process of selecting which database we'd use.

    What was best about this book is that it did cover fairly advanced topics. And that was helpful, as we could clearly see what features MySQL did not offer. Unfortunately for MySQL, it did not offer all of the features we wanted, and thus we instead went with PostgreSQL.

    • by Dan Ost (415913)
      Did you run into any gotchas with PostgreSQL or is it really as full featured and
      easy to use as people claim?
  • by mlwmohawk (801821) on Wednesday August 23, 2006 @06:07PM (#15965961)
    I'm so tired of hearing about MySQL. Every last improvement they make for it, is completely pointess as more advanced, and free, databases are so far ahead.

    If all you want to do is "select * from mytable where col='foo'" you are wasting your time.

    My bigest beef with MySQL isn't its limitations, but the people who learn these limitations and never realize a relational database and query language is SO POWERFUL.

    So, they get an order of magnitude more data than needed from MySQL, process it in some scripting language, take the reduced data set, make another query to get what they wanted. In a *real* database, you'ld just construct the query correctly.

    Every time I see a MySQL cconnection error on a web site, and you see them every day, I just think the designer is a moron.
  • Is that guy's name really Pipes? It's like a bad pron name only this is a bad programmer name.
    • by Somatic (888514)
      I actually just loled. Mod parent funny.
    • by Jay Pipes (997549)
      Yes, unfortunately, my name really is Jay Pipes :) I suppose me being a programmer is better than me going into the plumbing business, which unfortunately my father was in for 30 years. No joke.
  • by lkcl (517947) <lkcl@lkcl.net> on Thursday August 24, 2006 @05:03AM (#15968303) Homepage
    specifically, don't use it for 'comprehensive' projects, involving many programming teams and/or a hundred tables.
    don't even consider using MySQL 5 for at least another four years, for such projects, until it matures - and by that time, PostgreSQL, an already mature product that has had Views, Triggers etc. right from the start, will have had even more rigorous testing.

    see http://advogato.org/article/894.html [advogato.org] for details.
  • 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.

    Yeah, I heard there was some fancy-GUI interface to it...personally I like doing it all by hand, at least after the initial layout is set. I've been doing that since around 2002 though, so I'm pretty us

"Our reruns are better than theirs." -- Nick at Nite

Working...