Review: MySQL and mSQL 75
| MySQL & mSQL | |
| author | Randy Jay Yarger, George Reese, and Tim King |
| pages | 506 |
| publisher | O'Reilly |
| rating | 8.5/10 |
| reviewer | Dan Weeks & DocTechnical |
| ISBN | |
| summary | A good introduction to the world of relational databases and an excellent reference for MySQLand mSQL. |
First Review: Doc Technical
This is certainly one of the more orthogonal books I've read of late. Besides the obvious axis of MySQL and mSQL, the book also covers the implementation of these databases on Unix, Windows 95 and Windows NT. And it covers a wide variety of programming languages, including perl, Python, PHP, Java, C, and C++.
While this is certainly a good book, in fact a very good book, this wide coverage means that the average reader may need to skip around a bit to get to the parts of the book they need. A Linux perl mSQL programmer will necessarily take a different path through the book then, say, a Windows NT mySQL database administrator.
Not that straying down the wrong path is always a bad thing. It was interesting to read about the quirky differences between different OS implementations.
"Windows 95 leaks about 200 bytes of main memory for each thread creation." [page 41]
"...[D]atabase and table names are case-sensitive under Unix and case-insensitive under Win32." [page 43]
The book is divided into three broad sections: "Getting Started with MySQL and mSQL", "Database Programming", and a final "Reference" section which spans fully half the book. O'Reilly's high standards for editing, layout, writing, and clarity are all evident throughout.
What I Liked Best
For MySQL users, this book may appear to present a bit of a quandary. MySQL already comes with a 400+ page reference manual, and a quite nice one at that. But actually, the O'Reilly book covers much material the manual doesn't.Chapter 2 covers relational database design, and serves as an excellent introduction for the uninitiated. Some college texts could learn a lesson on clarity from the authors' explanation of normal forms.
Chapter 6 has an interesting, short history of the development of SQL.
Chapter 7 describes some of the other free SQLs available, and also provides some insight into those features that MySQL/mSQL don't provide: things like stored procedures, triggers, transactions, and subselects. This chapter is useful for people trying to decide which SQL engine they need. If your sitting on the fence, trying to decide on MySQL/mSQL versus a commercial SQL offering, this chapter may help you decide.
The book's cover declares "Databases for Moderate-Sized Organizations & Web Sites", and the book delivers on this promise by including web-oriented sections on general CGI programming, Perl, and PHP.
The second half of the book provides a good reference to the MySQL/mSQL API's for several languages, as well as the MySQL/mSQL utilities, and a good reference for SQL itself. Most of this information is available elsewhere, and in more detail, but it's useful seeing the various language APIs presented side-by-side, particularly if you're not sure what language you might want to use. I've been contemplating Python programming for a while, and the simplicity of its MySQL API is certainly seductive.
What's Missing
It's hard to find fault with the material included in the book, but I was surprised by some of the things that were left out.There really is no ground-zero, simple mySQL/mSQL tutorial. For people beginning with a new SQL engine, it would be helpful to have a chapter that holds their hand, showing them how to create a database, then create a simple table, the insert records into the table, using the mysql utility. Tutorials are available for MySQL on the net (see www.mysql.com) and one is provided in the MySQL Reference Manual.
The book covers programming using a wide range of languages, but arguably one of the most popular languages, C, seems to get comparitively little coverage. There are only about six pages devoted to C programming, and one of those is a list of API functions. I would have welcomed more. [Admittedly, there is more on C in the book's Reference section, but this covers individual API calls, and doesn't provide any longer examples.]
There were a few rare cases in the book where I disagreed with the authors, or at least thought they needed to add a bit of additional amplification. On page 109, they state that:
"If you know that a lot of clients will be asking for the same summary information often... just create a new table containing that information and keep it up to date as the original table changes."
I have a bit of a nit to pick with this, as seven pages later they discuss the lack of a feature called triggers that would greatly simplify keeping a summary table in sync. Without a trigger, you'll have to devise your own method for keeping a summary table in step with the original table, which may be non-trivial depending on how often the original table changes and how often the summary table is accessed.
Summary
This book tries to cover a lot of ground, and so it necessarily hits turf that some subset of readers won't care about.For the seasoned MySQL programmer or database administrator, this book is a fine companion to the Reference Manual. With its clear introduction to SQL and relational database design, it also makes a good introduction to new SQL users in general.
Second Review - Dan Weeks
The Scenario
There comes a time in every project when storing and retrieving data from flat files or proprietary formats (i.e., MS Excel) is no longer feasible. A Relational Database Management System (RDBMS) would be great, but Oracle, Sybase, and Informix can't be justified because of the cost. Along comes MySQL and mSQL, two of many freely available Database Management Systems. While neither of these DBMS's are as full featured and robust as their more mature brethren, they can definitely hold their own in the world of databases. For small and medium sized organizations and web sites either of these DBMS's can provide a sufficient level of functionality and flexibility to store and retrieve your data.What's Bad?
The only shortfall I could find is the lack of references to other, more advanced books on the subject of database design and normalization (although that probably doesn't fit in with the publishers motives, but it would be nice). The book is well rounded and all of the authors are very knowledgeable and well written.What's Good?
The first thing that struck me as absolutely wonderful about this book is the structure. By breaking the book into three sections the authors have allowed for many different database users to find this book valuable.Getting Started with MySQL and mSQL
The first section, Getting Started with MySQL and mSQL has everything the novice needs to at least get one of the packages up and running so that they can experiment with a database system. The authors do a great job of making sure that the reader can skip sections if they don't pertain to them. Introductory topics like What is a Database? and History of MySQL are essential in making sure the subject matter is well rounded and accessible to everyone (especially to people like myself that did not take database classes at university). Later chapters explain and detail database design and normalization in a manner that is easy to understand so that the first databases you build won't suffer from repetition and data inconsistencies. The authors also do a good job of explaining SQL and specifically the variants that MySQL and mSQL use.One of the high notes is the single chapter, Other Mid-Range Database Engines. Not only do the authors recognize that there are other database engines out there, they also point out what features MySQL and mSQL lack.
Making it Go
The second section of the book, Database Programming, is a well written set of chapters that start off with the architecture of databases and client-server application and how they relate to data processing. The authors then quickly take you into the guts of interfacing with the database. They cover CGI, Perl, Python, PHP and other embedded HTML styles, C/C++, and Java. While I have only ever used Perl, Python, and C to interface to a database I can say that the chapters on the other API's seem to do just as good a job and at least allowed me to understand (if even in the most simple of terms) how those languages function in relation to your database engine of choice.We all love our nutshell books, especially the XXX In A Nutshell series because they are great references. The foresight of the authors is incredibly prevalent in the third section, Reference. The authors actually took the time to make a ...In A Nutshell type of reference and then stick it into the book. Reference chapters that i have found invaluable so far are SQL (which includes separate sections for MySQL and mSQL's variations), and MySQL and mSQL System Variables. Other sections include C, PHP and Lite, Python, Perl, JDBC, and programs and utilities associated with MySQL and mSQL.
So What's In It For Me?
If you are at all interested in database programming or you run a database at a small- to mid-sized organization or web site then this book is a must have. For those people that are in need of a little instruction on database design and normalization this book would be a good start. If you have been working with either MySQL or mSQL for a while then this book may be a bit basic for you, but the reference chapters will more than make up for the cost of the book.Purchase this book at Amazon.com
Table of Contents
Preface I. Getting Started with MySQL and mSQL 1. Introduction to Relational Databases 2. Database Design 3. Installation 4. MySQL 5. mSQL 6. SQL According to MySQL and mSQL 7. Other Mid-Range Database Engines II. Database Programming 121 8. Database Application Architectures 9. CGI Programming 10. Perl 11. Python 12. PHP and Other Support for Database-driven HTML 13. C and C++ 14. Java and JDBC III. Reference 229 15. SQL Reference 16. MySQL and mSQL System Variables 17. MySQL and mSQL Programs and Utilities 18. PHP and Lite Reference 19. C Reference 20. Python Reference 21. Perl Reference 22. JDBC Reference Index
Re:mySQL is as slow as mud (Score:1)
(Moderate down now, folks....)
Re:mySQL is as slow as mud (Score:1)
Surely, "Richard/Linus"
Re:A word of warning on PostgreSQL (Score:1)
PostgreSQL (Score:2)
---
Re:Sure: let's blame the database... (Score:2)
Anecdotal evidence seems to support this, as the main page usually loads quickly, while discussions with hundreds of deeply nested comments load much more slowly.
I *hate* MySQL. (Score:1)
I've got admin duties for Postgres, Sybase, and MySQL, and MySQL is the one that I dread the most.
Why is "createuser" or "sp_addlogin" something that is so difficult for MySQL to accomplish? Adding users should not require me to insert data directly into the system tables.
MySQL access rights may be cool, but what does it really have over pg_hba.conf? Does this awful setup really beat Sybase access rights plus ipchains?
I had to restore a copy of phpslash recently, and MySQL made it hell. I even went out and bought the O'Reilly book, but it was no help.
Down with MySQL!
Re:Orthogonal? (Score:1)
Re:screwy formatting (Score:1)
Re:mySQL C API (Score:1)
struct ST_ROW {
char *id;
char *first;
char *last;
}
struct ST_ROW *st_row;
while (row = mysql_fetch_row(result)) {
st_row = (struct ST_ROW)row;
printf("%s %s %s",st_row->id,st_row->first,st_row->id);
}
Re:mySQL C API (Score:1)
st_row = (struct ST_ROW *)row;
Re:tilting at windmills (Score:1)
Re:PostgreSQL (Score:1)
I'm considering using Postgresql for some upcoming projects because it offers some things that MySQL doesn't--Postgresql is more of a full-featured SQL database system.
MySQL can't be beat (yet) for speed, I understand. It's too bad that so many cool web toys rely on MySQL as a backend--maybe I'll end up having to run two SQL servers? This wouldn't be a problem if applications were designed in a DBI (Database Independent, for you non-Perl types) fashion.Introductory RDBMS book (Score:3)
An Introduction to Database Systems
by C. J. Date
Hardcover - 975 pages 7th edition (August 1999)
Addison-Wesley Pub Co; ISBN: 0201385902
I have the previous edition, and have yet to come across a general RDBMS related question that can't be answered by it. So if the mSQL/MySQL book doesn't act as enough of a primer for you, then I can strongly recommend this book.
Chris Wareham
MySQL under Windows (Score:1)
How does using MySQL and MSVC++ compare with DAO or ADO and VC++. We use an Access database with approximately 100 tables and 40 stored queries. With DAO in VC, it is almost trivial to retrieve a bunch of records with a query. How does the process with MySQL and VC compare?
I would love the opportunity to port our app to Linux. Unfortunately most of our customers are stuck in a Windows only world. Hopefully that will change....
EC
Re:Pronunciation Guide? (Score:1)
PostgreSQL: POSTgrehs-Q-L
A word of warning on PostgreSQL (Score:2)
Sure: let's blame the database... (Score:1)
Ugh (Score:1)
Isn't that taking localization a bit too far?
The obvious solution is to change your name to 'Red Hat Linux'. Then, you can sell stock in yourself!
--
QDMerge [rmci.net] 0.21!
isn't that what GRANT is for? (Score:1)
mySQL C API (Score:2)
The big advantage of mySQL is that it's a very low-overhead database. There is no GUI; the user interface is a program that lets you type SQL statements at a command prompt. If you're comfortable with that, I think mySQL's superior performance would win you over.
The mySQL API is easy to learn but it could be easier to use. At least in the raw form, you can't refer to fields by name. Here's how it works:
[Disclaimer: Code is likely to contain errors; I presently have an icky case of the flu and might miss something important. Proper code indentation is removed by the HTML interpreter
Access
db = opendatabase("foo")
rs = db.openrecordset("select id, first, last from clients")
while not rs.eof
debug.print rs!id, rs!first, rs!last
rs.movenext
wend
rs.close
mySQL
socket = mysql_connect(NULL, "localhost", "id", "password");
mysql_select_db(socket, "database_name");
if (mysql_query(socket, "select id, first, last from clients") 0) {
printf("Query didn't work: %s\n", query);
exit(0);
}
result = mysql_store_result(socket);
while (row = mysql_fetch_row(result)) {
printf("%s %s %s", row[0], row[1], row[2]);
}
mysql_free_result(result);
mysql_close(socket);
As you can see, the biggest difference is having to use numbers instead of character strings to reference field names. You could probably write a function that would search the list of returned fields for a string, but it would slow things down a bit.
It can be very confusing to remember which field goes with which number, especially when you have a query with lots of fields. I find this is by far the most common problem I have, and it's certainly not anything that happens under Access.
I think, however, that you can use interpreted languages like Perl or Python and fix that problem. Performance should still be better than with Access.
D
----
mySQL versus Oracle for Slashdot (Score:3)
Oracle was designed to be a database to use when data integrity was more important than speed. If you were a bank, and you had to make sure the balance and transaction tables were correctly updated whenever a deposit or withdrawl was made, you would use Oracle.
Since Slashdot does not require this kind of multiple table update, upgrading to Oracle would not help with Slashdot's actual troubles. Obviously, using a speed-oriented database is the correct decision in this instance, as it is for many other web sites.
More hardware and a fatter pipe are more likely to fix the problems - and I think they're both on the way.
D
----
A book about MySQL should mention PostgreSQL (Score:2)
MySQL developers will have to wake up to the fact that the need for transactions isn't going to go away, ever. Granted, there may be some performance loss with this feature, but it COULD be optional.
--
Disappointing (Score:1)
For one thing, fully one-third of the book is basically man page extracts and API lists (67 pages ALONE for JDBC!!!). Secondly, while database creation and functions are competently developed, there is very little at all about practical issues and approaches to real-world problems. Compare this with, say, O'Reilly's own Annoyances volumes or the Nutshell books.
The lack of familiarity of the authors with the history and extent of the SQL standard were readily apparent. If this were admitted, though, they would have had to highlight the fact that mSQL falls far short in this regard, where MySQL does an excellent job. For example, the authors state on p.234:
"The [MySQL] CREATE INDEX statement is provided for compatibility with other implementations of SQL. In older versions of SQL this statement does nothing."
This is either plain wrong or sloppy editing. CREATE INDEX is the correct statement in the ANSI SQL92 standard, and variants such as MySQL's ALTER TABLE ADD INDEX are equivalents.
The book was clearly rushed to press, with many obvious typos and some difficult to understand layout decisions. For example, the important distinction between MySQL and mSQL reference sections is minimized to a simple subhead in the middle of p. 269. These should be separate chapters or at least designated subsections in the page headings. Otherwise it's quite easy to end up in the MySQL SELECT section rather than the mSQL one, for example.
Where the book is strong, in fact, is in the discussion of how to use various other tools such as Perl scripts or PHP to work in conjunction with MySQL or mSQL. The discussion of CGI is quite good, for example. So really, the book should have been called something like Web Publishing With MySQL and mSQL, because that is really the stance it takes. Nothing wrong with that, really, and it will be helpful to me in that sense, but I think we will have to wait for Paul DuBois' upcoming book to get a more definitive treatment of MySQL, which really deserves better than this surprisingly uneven O'Reilly offering.
Finally, a comment on the ever-present problem of "transactions." I had a long discussion with David Axelrod about this after the presentation yesterday. While I agree with their strategy of not including transactions in MySQL (at least not yet), because it allowed them to optimize the system in other ways, there is definitely a perception problem.
First, it is important to understand what "transactions" means. It is nothing more nor less than adoption of the SQL92 standard for COMMIT and ROLLBACK. These provide for multiple-statement units of work in database operations to provide referential and data integrity. For example, you don't want a financial transaction in a double-entry system to succeed only partially.
The problem is that "transactions" as defined by COMMIT/ROLLBACK is theoretically sound but practically "expensive" approach. In many cases I would say that the benefits of the formal approach can be matched with other techniques, thus avoiding the overhead which is considerable -- up to an order of magnitude in performance loss is not unthinkable with COMMIT/ROLLBACK. Even I was a bit skeptical about this until I got some insight from an Oracle engineer about the incredibly complex requirements of implementing it.
My guess is that 90% of those who think they need "transactions" would find they don't, and frankly, you can pay for a lot of programmer talent and/or database iron for the price difference between MySQL and Oracle, while accomplishing the same results.
-------
Re:mySQL is as slow as mud (Score:1)
to be fair not on my connection - the average page loads in a little under a second. OK so I do have a 2 meg line almost to myself most of the time... but it looks a lot like your delays are network bound not software bound.
Tom
It Does (Score:2)
And the same chapter is also quite clear that MySQL/mSQL lacks these and other features.
- Doc Technical
Re:mySQL is as slow as mud (Score:1)
If they can't even get the name of the OS right, I wonder how good the actual port is.
Foriegn Keys (Score:1)
Re:MySQL under Windows (Score:1)
Re:Foriegn Keys (Score:1)
But, foreign keys suck if you're looking for raw speed -- that's one reason MySQL is so damn fast; the programmers didn't have to implement foreign keys.
I just did a project that used 17 separate tables, heavily related, and I will say that I'll never try any such foolishness using a DB w/out foreign keys again. Oracle is huge and troublesome to administer, but for large projects, I think it (or equivalent) would be WELL worth it, especially for edu orgs, for which Oracle is free.
Re:Orthogonal? (Score:1)
MySQL and Coldfusion (Score:1)
I never say Sequel (Score:2)
/* My first completely unnecessary and useless post of the day! */
I wasn't quite as thrilled with the book (Score:1)
Some of the discussion is pretty light. Once you've got your data out a query, and are skipping through the result array, is it a *zero based* or *one based* array? The book should say, but doesn't.
The function references don't refer to one another. Once you've found the routine that gets data from a query result, you'd think there'd be a "see also: the routine that got you those results". But there isn't.
Not all routines are documented! And some that are documented are listed as "depreciated"!
I'll almost certainly get v2.0 of the book, though, assuming that it becomes more complete.
Kudos to O'Reilly! I sent in a list of problems, and they got right back to me!
Re:screwy formatting (Score:1)
I have it.. (Score:1)
Re:A word of warning on PostgreSQL (Score:1)
Re:mySQL is as slow as mud (Score:1)
I wonder when they're going to make "Oracle8i Enterprise Edition Release 8.1.5 for Dave." Imagine commercial software designed with just me in mind. Oh, and don't forget to include the source!
Dave
Re:MySQL and Coldfusion (Score:1)
JF
Re:mySQL is as slow as mud (Score:1)
I don't find the site performs that bad unless it's suffering under it's own
Woohoo! (Score:2)
Whoa, what? Now O'Rielly makes my
SirSlud
Re:It's a shame that Slashdot use closed-source My (Score:1)
official mSQL guide (Score:1)
Re:I *hate* MySQL. (Score:1)
Yeah, I found this method of setting up MySQL pretty inconvenient. The three-tables structure seems way overcomplicated to me and especially confusing when learning the system. The necessity to keep asking mysqladmin to reload in order to make the new grant tables work was a pain, too, especially when I forgot to do it and got confused. Erm.
And since localhost doesn't seem to be detected correctly all the time I often ended up having no access at all. I'd prefer a root user guaranteed to have total access available. Otherwise I have to shutdown and restart the server with no grant tables again, gagh.
Other than that I've quite liked using MySQL. For the money... :-)
Re:mySQL is as slow as mud (Score:1)
Re:screwy formatting (Score:1)