Slashdot Log In
MySQL Database Design and Optimization
from the stick-this-in-there dept.
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.
For DB-neutral SQL tuning... (Score:5, Informative)
PLUG: Which SQL queries are taking the most time? PQA home page [postgresql.org], download [pgfoundry.org].
Re:For DB-neutral SQL tuning... (Score:5, Informative)
Also check out SQL For Smarties [amazon.com] (love that name!).
EricDownload the free version of SQL Anywhere Studio [ianywhere.com]
Parent
common gotchas (Score:4, Informative)
Re:common gotchas (Score:2, Insightful)
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)
You're ne. .
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)
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?
Parent
Re:uhhh, what? (Score:2)
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)
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:3, Insightful)
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,
Re:uhhh, what? (Score:3, Insightful)
> 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
Re:uhhh, what? (Score:2)
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
Re:article NOT A RANT, those are legitimate gotcha (Score:4, Funny)
How true. For instance, emacs is a much better piece of software than vi.
Parent
MySQL and PostgreSQL RFD in news.groups (Score:5, Informative)
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
Vote yes, so they know there is support for a big 8 comp.databases.postgresql newsgroup as one does not exist yet!
Mod parent up! (Score:2)
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)
A few are floating around for £20 as well.
MySQL for beginners? (Score:4, Interesting)
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.
Looking for a good explanation of advanced SQL (Score:2)
Re:Looking for a good explanation of advanced SQL (Score:2)
One project of mine involved a supplier quoting system. Supplier were to log on to view new RFQs, quote on them, and see if their quote got accepting. The quote table had a supplier_id field, of course, to indicate what suppliers had quoted. What made me very nervous was that simple integer that prevented suppliers from seeing each others' quotes -- they were in competition with each other, and we had no doubt that they would d
A book review about PHP and Pear DB (Score:2)
Database design? (Score:3, Insightful)
Still needs lots of work (Score:4, Insightful)
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.
Re:Still needs lots of work (Score:3, Informative)
MySQL 4.1 series marked as stable [slashdot.org]
Re:Still needs lots of work (Score:3, Interesting)
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.
Re:Still needs lots of work (Score:3, Informative)
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
A few words from one of the authors. (Score:5, Interesting)
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.
Re:MySQL sucks (Score:4, Insightful)
Parent
Re:MySQL sucks (Score:4, Interesting)
Wow! So I guess other databases don't get Slashdotted? That's impressive! (note sarcasm).
MySQL is popular because its easy
You say that like it's a bad thing... is something better if it's difficult to use?
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.
Parent
Re:MySQL sucks (Score:2)
I'm getting off on a tangent here, but:
I don't think the grandparent is trolling MySQL's ability to handle heavy loads but rather its tendency to attract n00bs.
You should disable display of PHP errors on production web sites, for instance ("php.ini" even tells you to), and use the server's error log
Re:MySQL sucks (Score:2)
I'm not a fan of MySQL tho. Yes, it is fast... As long as you are only querying data.
If you have multiple users updating data, MySQL isn't such a good option.
Re:MySQL sucks (Score:2)
In my example, we saw the same speed difference with database creation (which had lots of reads and maybe 10% writes) and also when we used the database in our application, which was all reads.
I haven't really tried Postgres though, so I can't speak to it... I've got to play with sometime and see if it grabs me.
Re:MySQL sucks (Score:3, Insightful)
Re:MySQL sucks (Score:2, Insightful)
Re:MySQL sucks (Score:3, Interesting)
Not everyone does
FULL JOIN
Again, not everyone does
Check constraints
Still don't, or the application can
trigger logic
You know what I'm seeing a pattern here. I guess it seems that while MySQL doesn't do everything you want, it does enough of what other people want so it would seem that at least to other people, MySQL does not suck.
As far as not throwing errors, we've either used different versions, or your talking about something I haven't come across, since I've seen MySQL throw pl
Re:MySQL sucks (Score:3, Interesting)
First of all, I like and use MySQL. I have it running on two public web sites and I use at home for my intranet. It is fast, easy to use, and works well for my purposes. It may not be perfect, but I think it is good. Anyway, on with the reply:
Constraints, triggers, and stored procedures are important to have in a database. While I can make up for MySQL's shortcomings in application code, this is not a good idea. I should not have to validate data in the application. If I could put that code in the database
Re:MySQL sucks (Score:2)
Then don't use MySQL there, and that was my point. There's no shortage of DB products to use, so use the right tool for the job. I never said no one needed stored procedures, triggers or subqueries, I said some people don't need them. Every time a MySQL story comes up, people act like its the only DB around and they're forced to use it.
SAP seems to disagree that MySQL has no place as an enterprise database though.
Re:MySQL sucks (Score:2, Interesting)
> Not everyone does
but when you do, you are Sh^Hadly Out of Luck.
> > FULL JOIN
> Again, not everyone does
see above.
> > Check constraints
> Still don't, or the application can
I hope you'll pardon my putting this so bluntly, but this is an absolutely classical n00b mistake. In my experience, a useful data store doesn't have "the" application or "the" interface. It has several to start with and eventually grows many of them. That is why the data store itself
Re:MySQL sucks (Score:3, Informative)
Oh, and sequences. MySQL has the auto increment property for a field, but doesn't have sequences that can be independent of a table.
Re:MySQL sucks (Score:3, Insightful)
mySQL may not be appropriate in a mission-critical situation, but that does not make it bad for all situations; if you need speed in prescedence of everything else then mySQL is probably the right tool for the job. If you need data integrity, ACID compliance etc then PostgrSQL, Oracle etc are the right tool for the job.
Outright saying one or the other is a POS only makes you look stupud.
Genuine question: MySQL not for mission critical? (Score:3, Interesting)
My database app is low volume data traffic to/from the db.
Can anyone tell me where MySQL fails when it comes to mission critical stuff? I'm very eager to find out.
Bert
Re:Genuine question: MySQL not for mission critica (Score:3, Informative)
The lack of referential integrity means you cannot guarantee the state of the data in the database, unless you run a whole bunch of queries to check.
If there is only one way to get data in, through one application that can enforce the integrity on entry, and check it on retrieval, you may be OK. As long as you know nobody will ever be updating it directly in the database.
Re:MySQL sucks (Score:3, Informative)
At least they finally added support for subqueries. That was always the nail in their coffin as far as I was concerned.
Re:MySQL sucks (Score:3, Insightful)
And that of course ignores the fact that it's also technically superior - faster, proper data integrity, and real SQL (sorry, but for an SQL programmer, the kiddie pool syntax available in MySQL is The show stopper)
MySQL is the very thing open source is supposed to be against - software being used just because
Re:MySQL sucks (Score:2, Interesting)
2) You have any proof that Postgres is faster than mysql? Everything I've seen indicates that mysql is still *slightly* faster than postgres. Most of the systems I've worked with I haven't seen much of a difference. Personally I only use Postgresql for everything, but on other systems mysql seems to be about the same.
Re:MySQL sucks (Score:3, Informative)
MySQL with the release of version 4.0 changed the licence of it's libraries from LGPL (commercial code can link to these libraries) to GPL (commercial code cannot link to these libraries, anything that does becomes GPL). Any program you write that uses the mysql libraries is considered a derivative work by the GPL. So unless you write your own mysql libraries (to the undocumented wire-protocol of mysql 4.0), you have
Re:MySQL table still huge after deleting most reco (Score:2, Informative)
or you can look here
http://dev.mysql.com/doc/mysql/en/OPTIMIZE_TABLE.
No no no, your syntax is all wrong (Score:2, Funny)
INSERT INTO mysql_sucks (comment) VALUES ('MySQL isn't a real database so this book makes no sense');
No no no, /your/ syntax is all wrong (Score:3, Funny)
Re:No no no, /your/ syntax is all wrong (Score:3, Funny)
Re:Not from O'Reilly (Score:2)
I'd not trust Apress until I hear the whole story on this [simon-cozens.org] issue [simon-cozens.org] and see how it settles out, though. Probably fine, but...
Re:FireBird Rocks (Score:3, Informative)
Oh, and PostgreSQL is actually better for ANSI SQL compliance. It's one of the most SQL-standard compliant database I've ever seen (certainly more standard than Firebird). Call me when Firebird has TIMESTAMP WITH TIME ZONE datatype.