MySQL Database Design and Optimization

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.

Comments

MySQL sucks

[thammoud]

Long live PostgreSQL. I am just being a mature /.er

Re:MySQL sucks

[Man in Spandex]

nono. you're right.

MySQL is there cause people aren't willing to learn the full potential of SQL which postgresql takes advantage of.

In general, people try MySQL because
1) It's popular among php boards and its name is reknown
2) It's easy

Then you see those sites with mysql query errors when under heavy load or whichever silly programming mistakes.

MySQL is popular because its easy

Re:MySQL sucks

[razmaspaz]

MySQL is popular because its easy

Funny, I thought bashing certain software packages was popular because it was so easy!

Re:MySQL sucks

[Anonymous Coward]

Ok, but the others are really not that hard. Take Firebird for instance. It installs with a double-click. Its self-tuning, nothing for the user to configure if he/she does not want to. Just as easy as MySQL to integrate into PHP. And with a low memory footprint.

There really is no reason to stick with the hack job that is MySQL except for the fact that you already know it. I realize that is not an insignificant reason, but the next time you have to deal with the BS of MySQL, consider switching.

Re:MySQL sucks

[lateralus_1024]

3) because it's cheaper than MSSQL Server and performs as good if not better.

A good way save money for a larger company, and a
sometimes the only choice for a startup.

Re:MySQL sucks

[temojen]

3) It's probably already available at your hosting provider, whereas Postgresql probably isn't (vicious cycle with #1)

Re:MySQL sucks

[jarich]

Then you see those sites with mysql query errors when under heavy load or whichever silly programming mistakes.

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.

Re:MySQL sucks

[dfetter]

MySQL is not easy. Need a subquery? You're out of luck, pal. A FULL JOIN? Sorry. Check constraints? No dice. Throw an error instead of taking a bad guess when you've fatfingered an input? Oops. And God help you if you need some trigger logic for auditing purposes, because MySQL AB certainly won't.

Re:MySQL sucks

[ptlis]

Here's an old adage for you: Every tool has a job, and there's a job for every tool.

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.

Re:MySQL sucks

[DogDude]

And as an aside, I worked at a biotech. We had an 80 gig database with a quarter billion entries. We were running platform neutral SQL and used MySql, Oracle and MS-Sql server.

We ditched the "commercial grade" databases because they were to slow! MySql did a great job under enormous load.

You may be right there. I've heard good things about MySQL and smaller databases. Oracle, at least, is not really designed for small databases as well as it is for large ones, I think. All of the overhead doesn't really begin to provide value until you're using a good bit of data.

Re:MySQL sucks

[Michalson]

Then why not use PostgreSQL? For companies it's cheaper (MySQL licence for commercial use is almost as much as the basic Windows 2003 Server Licence), and for open source lovers it's even more free.

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 everyone else is using it, without regard for if it actually has any merit. MySQL is the Windows of the database world.

Re:MySQL sucks

[ShieldW0lf]

MySQL is popular because it is known and because every $20 a month web hosting service supports it. Period. It's not any easier than other databases... the fact that MySQL supports a limited subset of SQL and only allows you to use the basics doesn't mean that more powerful databases don't also let you use the basics.

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

[0racle]

Need a subquery
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 plenty of errors.

Re:MySQL sucks

[chochos]

Don't forget stored procedures. MySQL doesn't support them (I think the new version is going to, though). On PostgreSQL you have their own SP language or you can write the procedures in C... also, I think transaction support is not as good in MySQL as in other databases, but I don't know for sure.
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

[typhoonius]

I'm getting off on a tangent here, but:

Then you see those sites with mysql query errors when under heavy load or whichever silly programming mistakes.

Wow! So I guess other databases don't get Slashdotted? That's impressive! (note sarcasm).

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 instead (or a separate log for PHP if you prefer), but few webmasters seem to bother anymore. It's also easy enough to catch MySQL errors and code pretty error messages for them for when your site is getting Slashraped, so there's no excuse for letting PHP barf cryptic database error messages everywhere (no matter what you're database server is running) as many sites do.

Re:MySQL sucks

[Anonymous Coward]

MySQL runs on Windows, and lots of devs develop on windows.

Postgres doesn't yet unless you've got cygwin, and the version of postgres that will run on Windows (version 8, I think?) isn't out yet.

But it's pretty close, so I'll try it and then wait a few months until other people have tested it for me ;)

Re:MySQL sucks

[archen]

1) I don't think you're required to buy a MySQL licence unless you need other licence requirements or support from the company.

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

[Anonymous Coward]

Ever here of a product called MAX DB, put out by MySQL AB? It does all those things, and more.

Re:MySQL sucks

[Anonymous Coward]

MySQL is the very thing open source is supposed to be against - software being used just because everyone else is using it...

You've clearly read Slashdot at least once, since you're posting here. What rational person could observe the mindless conformity of Slashdrones and then expect nonconformity and critical thought from the same group of people?

Open source amounts to a few hundreds projects making worthwhile contributions to society, while mindless code monkeys uselessly scratch their own itches in the the other 6.3 million projects. If the filthy bastards would take a shower, they'd have fewer itches.

Re:MySQL sucks

[Anonymous Coward]

And PHP is the MySQL of the language world. Sweet irony.

Re:MySQL sucks

[Lenolium]

A reply to #1 (because I have no idea what #2 is really like, I've heard stories both ways)

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 to buy a license for anything not open source.

This was the biggest reason for my move to Postgres. I make my living building web applications for people, my clients get the source code so that they can make modifications, but not the right to redistribute the source code (unless I use anything GPL, then they get the full GPL licence). So it's nice to be able to use a proper database server, and support the developers of that database server without having to open the source of everything I create. (Most things I create however, would just be another useless entry on freshmeat, so I don't feel too bad about not contributing these things back)

Re:MySQL sucks

[The Snowman]

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, it will be more robust. Not only would that provide a single point to validate from the application, it would also provide validation for scripts and console access.

Full joins and subqueries help, but in a different way. I should not have to execute two queries to get one result set (minus intermediate data). Queries should be atomic -- one piece of code that executes together. For example, let's say I have two queries that could be combined with a sub select. In between the queries running, something changes the data, invalidating my intermediate values and second query. While the underlying data is fine, it appears to the user that there is data corruption. The second query could return incorrect data or just error out for no valid reason. Sure, most nested selects are not necessary, but sometimes they are. Full joins are not usually necessary, but in about 0.0001% of the queries they are.

Maybe for the typical blog site these features are not all that critical, but for enterprise-class databases they are. The database application I use at work is bad enough in Oracle (poor DB design), it would fall apart at the seams in MySQL. Constraints, triggers, etc. help keep it working until we can reengineer it.

Re:MySQL sucks

[FuzzyBad-Mofo]

You call 80 GB a "small" database? Granted, I'm sure certain entities like credit reporting bureaus and the federal gov't have terabytes of data, but 80 gig seems fairly large to me..

Re:MySQL sucks

[jcdr]

Just to inform you, a few weeks ago the server I manage crashed despit the mirroring (one disk failed, and the ext3 filesystem has corrupted entry).

The machine have mysql and postgresql databases. result is simple: almost all mysql users have trashed tables, absolutly _NO_ users have any glitch with postgresql. If your data have any values, forget mysql, only use postgresql. Periode.

For me, data integrity is the highest priority, all other aspects are just cosmetic.

Re:MySQL sucks

[dfetter]

> > Need a subquery
> 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 has to maintain any data integrity/business rules inviolably. The alternative is to keep n (for some large n) code bases perfectly in synch and functioning exactly the same way.

> > 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.

It doesn't do enough of what people need when they're doing things that involve money, for example. There just isn't any way to use MySQL for an application that requires Generally Accepted Accounting Practices (GAAP) short of building a true relational database as middleware.

> 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 plenty of errors.

See the "gotchas" page, so often quoted here, re: the failure to throw errors when it's supposed to.

Re:MySQL sucks

[Johnno74]

Its a troll :)

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

[0racle]

for enterprise-class databases
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

[jarich]

It could of been a troll... I just assumed it was someone who worked with really big databases all the time and thinks his world is everyone's world.

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

[timmarhy]

access is also popular because it's easy. can you see the trend?

Re:MySQL sucks

[jarich]

I know this half troll, but you've got to admire someone who thinks his favorite application can recover from a disk failure.

I would be curious to know how you think Postgress recovers data that no longer exists?

Perhaps a more likely conclusion to draw from your "experience" is that your MySql tables were on the part of the disk that crashed and the Postgres tables were not.

Btw, by the time you get out of high school, take a spelling class. You're worse than most of the posters here.

Re:MySQL sucks

[jarich]

I assume you only code in assembler?

Re:MySQL sucks

[Anonymous Coward]

If you need speed over everything else then just use BerkeleyDB and cut out the cutesy middle layer. (MyCuteLittleSQL;^) tm)

Re:MySQL sucks

[Anonymous Coward]

"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)"

I'd love to see your detailed explanation on this one. Faster?? Are you high? The planner in Postgres sux. Proper data integrity?? Why does it allow me to stick a value in a field that's supposed to be auto-incremented without producing an error??

Re:MySQL sucks

[Anonymous Coward]

Yes, database normally can recover from a crash since the real ones use something called a journal. When they come up in an inconsistent stater, they read the journal and figure out how to get into a consisent state. The poster you are responding to is not the only person to point out MySQL and data corruption issues. It is far too common to be considered a productioin database. Also, you don't use views, synonyms or more importantly, referential integrity??? Come on, the poster, who was probably not a native English speaker may not spell well, but at least he doesn't need to take a class on databases.

Re:MySQL sucks

[Anonymous Coward]

Aside from taking advice from an AC who says something "sux" (real technical term there. Care to back it up?) There are no "auto incrementing" datatypes in postgresql, only default nextval('somesequence'). Note the "default" there.

Before calling this "broken" ask yourself how you'd insert a row into a database with two columns having the same number, without having an external object to get that number from. I use this all the time in billing systems that deal with families. One field for the ID, one field for the ID of who gets the bill, usually the same. In postgresql I can use nextval('sequence') for the first field and lastval('sequence') for the second, in a single command. In MySQL InnoDB only appears to support a single AUTO_INCREMENT column, so I'd have to insert the record, locate the record and read its id, then update the record with that id in the second field.

Not only that, MySQL can only count by one, unlike postgres which can use whatever number is next in the sequence: positive, negative, or even zero.

Re:MySQL sucks

[Anonymous Coward]

PostgreSQL doesn't have stored procedures, yet anyway. When you talk about stored procedures in SQL, you aren't talking about a function that returns something (which postgres users have been able to define forever.) You're talking about a procedure in the Pascal sense: it performs a sequence of operations on the database, without having any result to return.

That said, its easily faked by having a function in postgres where everyone ignores the return value.

Re:MySQL sucks

[rackman]

I would disagree with the comment that it is not built for the enterprise. I work for a major telecom company and we use it to track SS7 signaling(Every call made in the US generates 5 events that are logged for traces) . Our vendor has load tested it extensivly. If they believe and I have witnessed it take a mass calling event and log it properly then I would call that reliable. Mass Calling is >=100,000 calls at 5 messages per call. Half a million entries in about an hour and a half should work just fine.

Re:MySQL sucks

[Anonymous Coward]

A transaction must make all queries (and updates!) atomic WRT all other transactions. You don't need subqueries to avoid seeing random updates.

I have no idea whether MySQL implements this properly, but given the idiocy they used to spew about relational integrity, I sure wouldn't bet on it. After all, giving wrong answers is usually faster.

Re:MySQL sucks

[Anonymous Coward]

PostgreSQL is so cheap that everytime MySQL is being mentioned at slashdot, a troller has to jump in and start selling something called "PostgreSQL" that most people don't seem eger to use, anyway.

If trolling in MySQL is the best way to sell PostgreSQL, well that says a lot of it's quality.

Re:MySQL sucks

[jadavis]

FAT32 can hold 80GB of mp3s with no problem, and FAT32 is not exactly Oracle.

It's not how much a DB can hold, it's how easy/fast it is to get exactly the information about your information you want; nothing more, nothing less. The ease and performance of getting such information must scale also, not just the amount the DB can hold.

Heck, you know what's a great database? /dev/null. It can take the INSERTs as fast as I can send them, and never bogs down my system with pesky I/O bandwidth, and never tells me it's full.

So, 80GB can be a lot if you have demanding requirements about the details of your data and the relationships with other data. If not, it's just some bytes (that you optionally want back). Then it doesn't matter, just use FAT32.

Re:MySQL sucks

[Mant]

Commercial DBs often require a degree of tunning. They may also be doing things like keeping transaction logs.

Plus if you are using platform neutral SQL that works on MySql, you are basically using a small subset of SQL. You could probably get much better perfomance using the imporved features they offer.

The questions is, do you need those things, are they worth the extra cost? If not, MySql is going to be fine for you.

Re:MySQL sucks

[jadavis]

I like postgresql, but thanks for trashing that guy's post.

PostgreSQL has great recovery from a power failure. But if the bytes aren't there, the bytes aren't there. In fact, if the inodes aren't there, as far as postgresql is concerned, the bytes aren't there.

I assume that MySQL has some basic ability to recover from power failure. I'd be a little concerned that the database would be inconsistant, but it would probably not be corrupt. I'd like to know if anyone has any experience with power failure corrupting a DB of either type (pg or mysql).

Re:MySQL sucks

[jadavis]

Access's backend DB sucks. Access does not suck. It's a valuable tool to manage databases and also to create client access software to distribute to the people who need to access the data. Web interfaces have made it less necessary, but I bet it's a lot quicker in Access still.

Re:MySQL sucks

[Anonymous Coward]

On PostgreSQL you have their own SP language or you can write the procedures in C...

Or in perl or python! Imagine using the same language you use for web scripting for controling data integrity, right there in the database where everybody has to/can use it, where it is neatly wrapped inside your transactions, and where it can do any work with you data to reduce it to a sane size dataset before being send accross the line.

Re:MySQL sucks

[jcdr]

No, mysql is just a dangerouse code for your data. Just list how many filedescriptors mysql keep open on all the tables it uses. For exemple on my server now there is 148 filesdescriptors open from mysql, majority of them are on tables.

Now if you look at postgresql process, you see only a few pipes and socket and only two logs files open, no tables. Even if you make some big query, is not easy to see filedescriptors open on table from postgresql. Postgresql take care to protect at most as possible your data.

The probability to trash a posgresql table is not null, but the window is very thin. Mysql instead manage to have almost 100% probability to trash your data. What happens to my serveur data is just the consequence of the design of the code running on it.

Re:MySQL sucks

[Gi77 B4t35]

MySQL is popular because its easy

Just like me in high school.

Re:MySQL sucks

[Anonymous Coward]

I'll back this up, i look after a lot of database servers + web servers. Never seen postgresql get trashed, even under the worse situation (raid mirror fail, filesystem courpted, most files on partiation are unreadable. postgresql is still running ok??)

I've repeatily seen mysql databases die with minor issues like server under too heavy a load and to many writes at same time, or unclean shutdown etc.

Though the one bonus of mysql db's i've found is that you can move there tables between different archetures without dump and restore. Not that you should be doing that anyway. Only database i've seen where this actually works.

Re:MySQL sucks

[Anonymous Coward]

Postgresql keeps a resonsible journal tables stretching back a few days. I've found recover possible before with the journal logs when parts of disk arn't there. Its not a disk error but file system error. If mysql sync'd better and had journaling it would be a lot more reliable.

Re:MySQL sucks

[Hognoxious]

SAP seems to disagree that MySQL has no place as an enterprise database though.

If you're speaking of MaxDB, it's distributed by MySQL but I thought it was a different product - basically a rebranded version of SAP DB - a descendant of Adabas.

Re:MySQL sucks

[edittard]

there's a job for every tool.

Usually editor, at slashdot.

Re:MySQL sucks

[Anonymous Coward]

And yet they both get the job done, day in and day out. Imagine that!

Re:MySQL sucks

[Anonymous Coward]

Take your condescending attitude and fuck off, already. You're not helping anyone at all. You're just being an asshole and the world has enough of those already.

In fact, since you've provided absolutely NO constructive critism at all, I'm inclined to think that you know absolutely nothing about the topic and are just playing the "everything except the industry standard 'best' is just a toy" approach so that other elitists will think you do.

Re:MySQL sucks

[Anonymous Coward]

No, MySQL really will trash your tables if you drop power or otherwise end the program without giving it a chance to catch signals and flush buffers. MySQL keeps too many balls in the air for the sake of speed. The OP is right - integrity is FAR more important than speed if the accuracy and persistence of your data matters. PostgreSQL does a much better job of covering its bets -- logged transactions can be recovered even if the buffers aren't flushed before you quit. That's why their updates are slower, they're done right.

BTW - not all people who post here are from USA, and I'd like to see _you_ spell properly in portugese...

Re:MySQL sucks

[Anonymous Coward]

Yes, I know there are no auto-incrementing datatypes per se, it creates a sequence for that function, I work with Postgres everyday.

So a sequence is created, the only way Postgres can implement auto-increment. Then I insert a row where I specify a value for a column that has a sequence function and it takes it. In my mind that should generate an error, I'm inserting data where I shouldn't. I guess you don't think it's a problem.

Yes, the sequence flexibility is better than MySQL, but I've never needed a field to count up by 3's so it doesn't matter to me.

(P.S. In MySQL you reference the LAST_INSERT_ID for your example)

Re:MySQL sucks

[0racle]

I believe that its the other way around. Its based on MySQL and used for things of this nature. I think that I saw it on MySQL's site before the SAP deal, but I could be wrong on both counts.

Re:MySQL sucks

[ahmusch]

However, this is slashdot, where open-source is good and proprietary is bad. Therefore, a novice will see that MySQL and Oracle are both databases, but one's open-source, so MySQL must be better for their critical data and systems.

Whoops.

SAP probably still sells more systems that run against Oracle and DB2 than against MySQL. Further, let's not hold up SAP as a shining example of openness -- unless you've ever supported it, you do not know the horrors that lie within. If you ever wanted to see a fascist company from a support perspective (oh, you didn't follow our recommendations? No support for you!)

MySQL is an excellent alternative to Access or as a simple backend for data without ACID or sophisticated design.

To use an analogy, it's a garden trowel. Use it to dig up your flower box. However, it's not the right tool to till or cultivate a cornfield.

Re:MySQL sucks

[ahmusch]

Hell, DB2 didn't have sequences until version 8. Of course, one can semi-implement them in databases which don't support sequences by:

Create a table with a single IDENTITY/AUTOINCREMENT column.

Instead of fetching from a sequence, do an insert into this table, then select using whatever your system is for getting an IDENTITY/AUTOINCREMENT column back. Delete the row when done to prevent you from having a giant table of sequential values.

When you want to reset the sequence, drop and recreate the tables.

Re:MySQL sucks

[jadavis]

I don't think you understood my post at all. I wasn't advocating oracle, I was just pointing out that an 80GB database is quite common, and in order to impress someone with a database, you need to show what you can do with 80GB, not that you have 80GB.

Re:MySQL sucks

[Alarion]

You can do the same thing in MySQL.

It's not a problem. I think every database allows this.

And for your comment to his example, you still have to use two queries (one insert, one update) whereas with Postgres (and Oracle) it would only be one.

Re:MySQL sucks

[MikeBabcock]

Agreed. I've oft seen it discussed in the Gnome development circles (nothing against KDE, I just don't read their dev lists) that a killer app would be an Access-like front end to MySQL/Postgres/etc. for Linux (or even platform agnostic). Access is a great system for designing small to medium sized databases with. The back-end blows under load.

Re:MySQL sucks

[MikeBabcock]

I have fully sync'd MySQL binary logs, rotated, lasting 7 days, on a seperate partition, mounted with sync on. I've had hard power drops and not lost data. I lost data on MyISAM tables with no logging a few years ago, but I wouldn't expect that to have worked anyway. I actually had a query log so I could reconstruct the data myself anyway from a tape backup + log files.

I don't expect software to survive situations the OS might not even survive. My servers are battery-backed, but I still use tapes. I use RAID-1, RAID-10 or RAID-5, but still use tapes. I have log-structured filesystems, but I still use tapes.

No tape backup, no security.

Now a binary logging system that writes directly to a secondary tape unit, that I'd like.

Re:MySQL sucks

[Anonymous Coward]

How the hell is that insightful?

Re:MySQL sucks

[chochos]

Of course you can do that. But it's a complex transaction, not very efficient. You have to insert, select, then delete, just to get a new value, instead of just selecting it from a sequence... and yes, you can write a function that inserts, selects, deletes and finally returns the selected value, so that you only have to select from the function, but internally it's doing the same thing...

Re:MySQL sucks

[chochos]

You're right, I got confused. They're functions. But like you say, you just ignore the return value and execute the function by doing a 'select function(param1, param2)' and that's it. You can define a function that returns void, like in C, so that's practically a stored procedure (except that to execute it you still have to do 'select function()'.
Functions in PostgreSQL can even return rows (at least in version 7.4 which I'm using, you can return a row). Outbound parameters are not yet supported, I hope they include that feature for version 8.

Re:MySQL sucks

[ahmusch]

I never said it wasn't a hack or somewhat inefficient -- but it's the best way to get that done in systems which don't support sequences.

Sadly, most people who work in systems without sequences never even consider their existence, so they use identity/autoincrement columns indiscriminately, and you can't undo that kind of design decision simply.

Re:MySQL sucks

[runderwo]

Perhaps a more likely conclusion to draw from your "experience" is that your MySql tables were on the part of the disk that crashed and the Postgres tables were not.

No, a more likely conclusion to draw is that the MySQL tables were partly in memory at the time of the crash, while the PostgreSQL tables on disk were kept in a consistent, recoverable state.

Do you think MySQL has speed benefits only because MySQL developers are so much better than all other RDBMS developers, and not because of speed hacks that put your data at risk?

Do you think robust RDBMS design is just dumb luck?

Re:MySQL sucks

[jarich]

you think MySQL has speed benefits only because MySQL developers are so much better than all other RDBMS developers, and not because of speed hacks that put your data at risk?

Nope. I was running MySql in a non-transactional mode... and that makes all the difference.

Do you think robust RDBMS design is just dumb luck?

Do you think every program that isn't your personal favorite must be "bad"?

Re:MySQL sucks

[Anonymous Coward]

> MySQL licence for commercial use is almost as much
> as the basic Windows 2003 Server Licence

Apples and oranges. Windows 2003 Server doesn't include a database.

And MySQL licensing doesn't include any per-seat or per-connection crapola either.

Re:MySQL sucks

[Anonymous Coward]

The existence of a non-transactional mode is a speed hack that puts your data at risk. You can save a lot of disk I/O by using a random number generator instead of your "database".

Re:MySQL sucks

[runderwo]

Nope. I was running MySql in a non-transactional mode

In other words, a speed hack. (Look at ACID)

Do you think every program that isn't your personal favorite must be "bad"?

That's a non sequitur. Why would I generalize my MySQL experience to other databases that I've never used? Don't try to shift the blame for MySQL suckage onto its critics.

Insert

[Anonymous Coward]

"MySQL isn't a real database so this book makes no sense" comment here.

Re:Insert

[razmaspaz]

Don't you mean

INSERT INTO slashdot (comment) VALUES ('In Korea only old people use databases');

Everybody wants to be just like me...

[Anonymous Coward]

I'm NAKED and FAMOUS!!!

For DB-neutral SQL tuning...

[tcopeland]

...SQL Performance Tuning 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, download.

Re:For DB-neutral SQL tuning...

[Eric Giguere]

Also check out SQL For Smarties (love that name!).

Eric
Download the free version of SQL Anywhere Studio

In Corea...

[Anonymous Coward]

...only old people use MySQL

No, MDBS

[www.sorehands.com]

No, old people use MDBS -- an extended network model database.

common gotchas

[RelliK]

right here

Re:common gotchas

[ajs]

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

[kfg]

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

Re:common gotchas

[Anonymous Coward]

The anti-mysql crowd might stop popping out of the woodwork when:

* MySQL AB admits their product is a "lite" database, and doesn't outright disparage basic features of databases as unnecessary (recall how MS tells the world they don't need tabbed browsing).

* Similarly, MySQL AB stops implementing features completely half-assed and actually pays attention to data integrity issues. Would you believe the core system tables aren't transaction-safe? (they use myISAM) But you weren't going to write any administration tools or scripts for mysql anyway, were you?

* The perception goes away that a half-baked lite database with worse data integrity than MS Access is the best the Open Source community can do, because there are better examples.

MySQL AB has still addressed almost none of the problems in that rant. It will go away when they fix their broken database.

I do agree that there needs to be a list of Oracle and Sybase gotchas and just plain warts to balance things. (the DUAL table has always been a heinously ugly wart in oracle, for example)

Re:common gotchas

[ajs]

"MySQL AB admits their product is a "lite" database, and doesn't outright disparage basic features of databases as unnecessary"

Well, actually they don't. What they've said is that their PAYING customers direct their efforts and those customers prioritize differently than the "MySQL sucks" crowd. It's not terribly surprising which one gets their features implemented first.

What they have said is that they will not implement features that come with (what they percieve to be) major performance hits without finding a way to do so whithout slowing down everything. In many cases, they have found those ways (e.g. by providing InnoDB table types, but still providing MYISAM tables).

Other databases also contribute to this by discovering ways to make the tradeoffs less painful (or even not at all painful). I applaud, for example, much of the work done by Oracle on their unique caching strategies, and while it's not open source, they still contribute valuable data points. PostgreSQL and other open source databases also contribute hugely, just as MySQL contributes to them (would PostgreSQL have moved beyond its difficult-to-admin stage if MySQL were not so damned simple to install and set up?)

We're the OPEN SOURCE COMMUNITY, and we really should start acting like it.

Re:common gotchas

[matman]

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.

Re:common gotchas

[Anonymous Coward]

this isn't funny, it's informative

Re:common gotchas

[Anonymous Coward]

No! We can't. As long as shmoes think MySql is ready for prime time others must speak up and shed some light. I still have to laugh at anyone who thinks mysql is more than a powerful toy. Sorry, but sometimes the truth hurts.

Re:common gotchas

[fitten]

Well, actually they don't. What they've said is that their PAYING customers direct their efforts and those customers prioritize differently than the "MySQL sucks" crowd. It's not terribly surprising which one gets their features implemented first.

Understandable, but this assumes that their PAYING customers know what they are talking about. I use MySQL every day and I've used RDBMSs quite a bit. The feeling that I get from MySQL is that they think a lot of "features" of RDBMSs are just "fluff" (like transactions, stored procedures, foreign key constraints at one time or another) and not really needed or important. In my experience, the most vocal supporters of MySQL know little about RDBMS requirements and/or database design and just think that any database is a "big place to dump data so you can query it out later". Unfortunately, they tend to approach every DB project with the same attitude. I shudder to think how many small businesses out there are running their payrolls, customer database, orders, and the like on myISAM databases. But hey... it's *fast*, which is all that these people think that matters.

I think MySQL is a great product for what its features are. Unfortunately, it is *not* an RDBMS and shouldn't be used in place of one.

Re:common gotchas

[ajs]

Ok, clearly I'm addicted to this thread... I need help ;-)

"Understandable, but this assumes that their PAYING customers know what they are talking about. I use MySQL every day and I've used RDBMSs quite a bit. The feeling that I get from MySQL is that they think a lot of "features" of RDBMSs are just "fluff" (like transactions, stored procedures, foreign key constraints at one time or another)"

They don't, and if you get the chance to listen to them speak about the development of the database at a conference or the like, I highly recommend that you do so.

What they've said is that those features are great, but their existing user base was made up of people who were able to extract value from MySQL specifically because things that required those features were not their core concerns.

MySQL is often used as a front-end cache for other databases, for highly volatile data such as Web content and other information which simply does not require those sorts of features, and which oftne suffers from their implementation.

That's not to say that MySQL should not implement those features, just that they must be taken slowly and carefully, and implemented in such a way that the existing user-base don't have to pay the price for features they didn't ask for.

For example, let me take one that you did not touch on: sub-selects. MySQL doesn't do sub-selects.

Of course, you can emulate them in your code like so:

select x from a where a.y in (select y from b where z = 1);
# Becomes:
create temporary table tmp1 select y from b where z = 1;
select x from a, tmp1 where a.y = tmp1.y;

This eliminates a key optimization that sub-selects can perform, but is otherwise identical. That key optimization is to implement the temporary table in such a way that values are returned before either statement is finished executing. This is a major win in terms of the time between request and first answer, but should result in roughly the same execution time overall.

In this case, the small win from the extra optimization and shorter code would have come at the cost of other MySQL development that existing, paying customers wanted more and the changes to select logic that would have allowed for lazy evaluation might have resulted in more checks along the way.

So the feature waited on the back-burner while they worked on things like InnoDB integration and full-text searching. Personally I see the results as a win, and in the end I'll get sub-selects, but not at the cost of features I need.

"Understandable, but this assumes that their PAYING customers know what they are talking about."

Not really. It assumes that implementing what their paying customers want will give them the time and resources to implement what others do. This is a good thing.

" it is *not* an RDBMS and shouldn't be used in place of one."

This is completely untrue. Seriously, unlearn this "goodfact" that's been circulating on Slashdot and other places, as it's simply false.

An RDBMS is a simple category of datastore which implements a style of set logic which relies on relations between like set elements. To put it in crude terms, anything that can implement the most basic SQL select with an inner join is an RDBMS. The logic that has been used to corrupt the term is this: We need more features for modern applications than simple relations, so RDBMS is an insufficient term. Thus, to be an RDBMS, you must perform some arbitrary (and often ill-defined) set of other functions. People have thrown in ACID (a term you don't hear as much any more since modern MySQL engines are ACID), replication, object oriented relationships, and any number of other features which are quite simply not part of the meaning of the term RDBMS.

Say what you mean. MySQL is most certainly a fully-functional RDBMS. That it may not have some of the features of some other RDBMS or even some features that MOST other modern RDBMSs give you is no excuse for arbitrarily changing the defintion of RDBMS.

Re:common gotchas

[fitten]

Thanks for posting a good reply. One thing you mentioned:

To put it in crude terms, anything that can implement the most basic SQL select with an inner join is an RDBMS.

I guess drew my attention. RDBMSs also have features that insure referential integrity, not just that you can do a JOIN... i.e. you can't insert that record because the value you have for fForKey is not in the set of values that exist in KeyTable.fPriKey. Similarly, you can't delete the record in KeyTable because a record in OtherTable still contains a reference to the record in KeyTable.

On a different note, there are issues about other features, such as triggers, which can make calculations inside a transaction block more effient than multiple transfers from a client to synthesize it, that I haven't seen discussed well. Not that I use triggers often, it's just an example.

Also, as I said before and you affirmed, MySQL does do a great job for certain types of applications, several of which you mention, but one of the scary things is when I see people use it to address problems that the lack of support in MySQL may cause issues. In the end, the work that these people do reflects badly upon the OSS work because you'll get folks saying stuff like: "Yeah, we hired some bozos to write some OSS stuff for us and we lost several clients' information and somehow a number of orders 'disappeared' from the database." It's not that you can't make the same mistakes in other RDBMSs, but it's that people who don't know what they are doing, but think they do, recommend the wrong tools for the wrong job while posing as experts. I've found that people who poo-poo transactions as being 'unnecessary overhead' tend to not understand what transactions are for, when to use them, and why/when they've made bad choices in the past when designing systems.

Re:common gotchas

[ajs]

I'm a bit tired, and it's been a long week. Pardon me if I'm getting gruf. No offense intended. I started off the week by spending my birthday in the emergency room, so I may not be at my conversational best ;-)

RDBMSs also have features that insure referential integrity, not just that you can do a JOIN... i.e. you can't insert that record because the value you have for fForKey is not in the set of values that exist in KeyTable.fPriKey. Similarly, you can't delete the record in KeyTable because a record in OtherTable still contains a reference to the record in KeyTable.

I understand that that's the myth that that's been floated on Slashdot for 3 years running. I want you to understand that I understand that.

Now, listen to me: that's not what an RDBMS is. It's just not.

Almost all of the existing ones out there DO do these things, but to say that, "you chose not to so you're not an RDBMS," makes no sense. These are design choices you make AFTER you decide to write an RDBMS, not criteria for determining if something is or is not an RDBMS.

This subtle rhetorical trick has been played on you because it works. It makes you think, "hey, if it's not an RDBMS then why are people using it like one?! They're using a toy as an RDBMS!" when in reality nothing could be further from the truth. Is MySQL limited? Sure, in some places it is. Is that bad? Ask the folks who started using Linux in 1998. Linux had a great deal going for it at the time, but it didn't have a journaling filesystem (AIX and Irix did). It didn't have decent multiprocessor support, and was unable to run on >2 processors (Solaris, AIX and Irix did not have this problem). It had a really bad NFS implementation (Solaris and BSD did not). And yet an entire industry was built, mostly on the back of this thing that many at the time were saying, "isn't really an OS." or "Isn't an 'enterprise class' OS."

"Also, as I said before and you affirmed"

You go on to make a fiarly long and winding statement, most of which I did not "affirm". Not sure which part of it you thought I was in agreement with, but suffice to say it's not a very big chunk.

"MySQL does do a great job for certain types of applications [...] people use it to address problems that the lack of support in MySQL may cause issues [...] It's not that you can't make the same mistakes in other RDBMSs [...people] recommend the wrong tools for the wrong job while posing as experts"

I just can't make sense of most of that paragraph, but it sounds like you're saying, "consultants who do their job poorly can cause the products that they specify to look bad, and when those products are open source that can extend to the entire OSS world."

If that's an accurate paraphrase, then I agree. I do not, however, see how that has much to do with what we've been discussing. I could, for example, specify a general purpose Linux system as a fileserver. Someone else then comes along and points out that a hardware fileserver like a NetApp or EMC would have been faster, more reliable and more scalable. I would have to grudgingly agree, and my specfication would reflect poorly on Linux. This does not mean that Linux is bad (it's a great technical or development desktop, application or utility server, etc), it just means that I should not have specified it in a place where it was ill suited.

Re:common gotchas

[fitten]

My error. I didn't make paragraphs where I needed them in that section. Here it is edited. I tried to mark the places where I changed things in []s:

Also, as I said before and you affirmed, MySQL does do a great job for certain types of applications, several of which you mention.

However, one of the scary things [] I [have witnessed is that] people use [MySQL as a solution where it lacks the proper features to solve the problem]. In the end, the work that these people do reflects badly upon the OSS work because [employers new to OSS will say]: "Yeah, we hired some bozos to write some OSS stuff for us and we lost several clients' information and somehow a number of orders 'disappeared' from the database."

It's not that you can't make the same mistakes in other RDBMSs, but [there are] people who don't know what they are doing, but think they do, recommend[ing] the wrong tools for the [] job while posing as experts. I've found that people who poo-poo transactions as being 'unnecessary overhead' tend to not understand what transactions are for, when to use them, and why/when they've made bad choices in the past when designing systems [using MySQL or any other RDBMS without using transactional processing even when available].

Re:common gotchas

[ajs]

Most of what I said still holds.

Take care and good luck with whatever RDBMS you choose. Personally, I try to touch them as little as possible these days.

Re:common gotchas

[Tony-A]

Referential Integrity:
from http://databases.about.com/cs/administration/g/ref integrity.htm

Consider the situation where we have two tables: Employees and Managers. The Employees table has a foreign key attribute entitled ManagedBy which points to the record for that employee's manager in the Managers table. Referential integrity enforces the following three rules:

1. We may not add a record to the Employees table unless the ManagedBy attribute points to a valid record in the Managers table.

2. If the primary key for a record in the Managers table changes, all corresponding records in the Employees table must be modified using a cascading update.

3. If a record in the Managers table is deleted, all corresponding records in the Employees table must be deleted using a cascading delete.

Rule 1 states that the President cannot be an employee, unless he's managed by, say, the janitor.

Rule 2 means that when your manager moves to a different position, you got moved.

Rule 3 means that when your manager is fired, you never existed.

Dupe.

[Anonymous Coward]

This is a dupe you fucking piece of shit terrorist!

Insert

[Anonymous Coward]

...In Korea only old people use databases comment here...

MySQL table still huge after deleting most records

[exhilaration]

I know this is kinda off topic, but it is kinda about optimizing MySQL, but I deleted 60% of the rows in a 60MB MySQL table on my webhost and the size hasn't changed. Is there some command to "pack" the space?

Re:MySQL table still huge after deleting most reco

[TimCrider]

OPTIMIZE TABLE `tablename`

or you can look here

http://dev.mysql.com/doc/mysql/en/OPTIMIZE_TABLE.h tml

MySQL and PostgreSQL RFD in news.groups

[Anonymous Coward]

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!

Re:MySQL and PostgreSQL RFD in news.groups

[Klync]

for every time that name will ever get mentioned, why not call it comp.db ???

UK People

[RobertTaylor]

If you are in the UK you can get the book here for 10% off and free delivery up to Dec 25th :)

A few are floating around for £20 as well.

No no no, your syntax is all wrong

[Megaweapon]

Insert "MySQL isn't a real database so this book makes no sense" comment here.

INSERT INTO mysql_sucks (comment) VALUES ('MySQL isn't a real database so this book makes no sense');

Re:No no no, your syntax is all wrong

[tzanger]

Noting that the comment column is supposed to hold an integer and MySQL still inserts this text without error makes it all the funnier. :-)

Re:No no no, your syntax is all wrong

[Klync]

Insert "MySQL isn't a real database so this book makes no sense" comment here.

INSERT INTO mysql_sucks (comment) VALUES ('MySQL isn't a real database so this book makes no sense');

I guess you'd need to do that as a nested query to get the "here" part in there.

Re:No no no, your syntax is all wrong

[Anonymous Coward]

I guess you're a faggot. I'm right.

Mod parent up!

[Martin Marvinski]

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

Re:Mod parent up!

[Cow Jones]

There are NO MySQL and Postgresql groups in the comp.databases.* hierarchy on usenet

That would depend on your news server.
My provider's server has 14 c.db.postgresql.* groups.
And come to think of it, so does Google.

Re:Mod parent up!

[Anonymous Coward]

"That would depend on your news server.
My provider's server has 14 c.db.postgresql.* groups.
And come to think of it, so does Google."

And they were "bogus", because they haven't gone through RFD and CFV. Marc, the mailing list/usenet person moved them to pgsql.*.

There is a whole thread on this in news.groups.

Re:Mod parent up!

[harmonica]

They're not official, so well-maintained newsservers don't carry them. But the group comp.databases exists, so there is group for MySQL and PostgreSQL in the comp hierarchy.

New Cliche

[mattkime]

SELECT fldDatabase FROM tblPEOPLE WHERE fldAGE = "OLD" AND fldKOREAN = TRUE;

RESULT (1 row)

MySQL

No no no, /your/ syntax is all wrong

[PhrostyMcByte]

escaping is good.

Re:No no no, /your/ syntax is all wrong

[Megaweapon]

Bah... Trying to be funny while having a headache doesn't work well.

IN COMMIE NORTH KOREA

[Anonymous Coward]

MySQL only uses old people!

MySQL for beginners?

[owlstead]

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.

Re:MySQL for beginners?

[Anonymous Coward]

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.

However in database design this should be adjusted: learn the underlying theory, get it right, get it working, get it optimized. You have to get it right FIRST .. what do you do once bad data gets into the database?

PS: what is an "RDBC"??

Re:MySQL for beginners?

[Anonymous Coward]

Saying "MySQL for Beginners" is redundant. MySQL is only for beginners. The big kids use fancy databases with things like sub-select and such.

Re:MySQL for beginners?

[Anonymous Coward]

MySQL does have subselects and has had them for a while. If you'd read the book being reviewed here, you'd know that.

Looking for a good explanation of advanced SQL

[lawpoop]

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.

Re:Looking for a good explanation of advanced SQL

[deKernel]

Stored procedures are great if you have a query that gets called over and over because the query is already compiled and ready to go.

Views are great if you have a dataset that just doesn't change much because it allows for the data to be sitting there ready. Example: we have some data that only changes at most once a day, but the dataset is needed continuously.

Triggers are great if during the insertion of data requires a bunch of work because again the triggers are already compiled and ready to go.

Granted, this is with SQL Server 2000, but this should apply to all.

Re:Looking for a good explanation of advanced SQL

[Anonymous Coward]

You should first learn and understand the relational model. I.e., the underlying theory. There are plenty of resources on the net and in DB textbooks. Many of the advantages of postgresql will become immediately clear.

One aspect, views, is a *very* important feature. It is the database world's version of encapsulation. For example, suppose you have a Person entity with the following attributes:

(id, name, shoe_size)

Then you have an Employee which has the following attributes:

(id, name, shoe_size, salary)

And a Janitor (yes I'm making this up as I go along :O):

(id, name, shoe_size, salary, favorite_mop)

You would also want a constraint that says ID's are unique among People, Employees, and Janitors (because they are actually type and sub-types).

In PHP you might model it like this:

class Person {
var $id;
var $name;
var $shoe_size;
}

class Employee extends Person {
var $salary;
}

class Janitor extends Employee {
var $favorite_mop;
}

How do you handle it in the database? Easy, you create a Person table:

(id, name, shoe_size)

And an EmployeeDetails table, which references this table:

(id, salary)

And a JanitorDetails table which references EmployeeDetails:

(id, favorite_mop)

Now you can use views like this (making up a simpler non-SQL syntax):

CREATE VIEW Employee AS Person JOIN EmployeeDetails;
CREATE VIEW Janitor AS Employee JOIN JanitorDetails;

Now you've got a table for each entity, BUT you still have data integrity (for instance the IDs are unique across the tables.. you can now demote a Janitor to an Employee by deleting one row from JanitorDetails). And if you don't want data integrity, why are you using a database?

You can also UNION these tables with some constant column values to get a table of "all people":

CREATE VIEW AllPeople AS
Person EXTEND (salary: NULL, favorite_mop: NULL, type: "Person")
UNION
Employee EXTEND (favorite_mop: NULL, type: "Employee")
UNION
Janitor EXTEND (type: "Janitor")

(it doesn't matter what is in those non-applicable columns so I chose NULL).

This results in a view with these columns:

(id, name, shoe_size, salary, favorite_mop, type)

Where "type" is a column telling you what class of object to create for each row.

Now you've got a "denormalized" table which is easy for generating reports, etc. But your underlying tables are still well-designed.

It gets even better if you have *updateable* views, which unfortunately Postgresql doesn't support directly (it is a weakness of SQL in general that you can't calculate which views are updateable, even when they are obviously updateable like these are, because SQL doesn't guarantee certain things).

Stored procedures and triggers don't belong in the database unless they are used for integrity, so don't worry too much about those. Sometimes triggers can be used for enforcing cross-table constraints (i.e., a row in table X can't be inserted unless there are a certain two rows in table Y, etc). I don't know if this is possible in postgresql.

Re:Looking for a good explanation of advanced SQL

[Anonymous Coward]

sprocs are basically compiled sql statements, which are usually cached and the "plan" is already worked out, therefore much faster.

triggers are also sql statements, but follow sort of "if this happens then do that" rules. ie a user enters a transaction which updates a value, this then fires the trigger to update a bunch of other values based on some formulae, then inserts an adjustment figure in another table for audit purposes. The bonus with it being controlled by the rdbms, is if something needs changing in the formulae, it only needs to be changed once and will then take effect immediately, no client updates etc. Not to mention being faster than sending loads of traffic between client + server.

For me sprocs and triggers are a must, too many data rules and processes are controled client side in some projects. Get the database doing the work, thats what its for, cut down on admin and network traffic.

Postgresql does do triggers, sort of. You have to write a sproc which will do the sql statements, then the trigger is written to simply fire the sproc on certain conditions. This differs to sql server in that the code for the trigger is not actually part of the trigger.

Re:Looking for a good explanation of advanced SQL

[lawpoop]

This might be the answer to one problem that has been particularly vexing to me:

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 do whatever necessary to win the business. They were overseas, so really no legal protection there.

I know that databases have column level permissions, but what I really needed was *row* permissions -- the supplier's database account would only have permissions to SELECT rows that had a particular supplier_id.

What database feature provides this capability?

Re:Looking for a good explanation of advanced SQL

[undef24]

I'm not sure about mysql, but in other databases if you've got a function that can return the current login name, then you could use a view that selects records based on the current users login name. IE:

SELECT * FROM records WHERE owner_id = CURRENT_USER()

Re:Looking for a good explanation of advanced SQL

[davegaramond]

For me, views are mostly valuable to significantly simplify queries. With views you can accomplish your complex query in steps: create a view with some criteria, then do a select on that view, and so on.

Then there's materialized views. This one is really useful for performance while still keeping your model clean (no more need to do manual triggers to update summary tables, etc).

Re:Looking for a good explanation of advanced SQL

[jadavis]

Views are great if you have a dataset that just doesn't change much because it allows for the data to be sitting there ready.

I think you're talking about materialized views. Views in postgres are, as of now, just query rewriting, so they serve no purpose other than organization.

However, views are a very valuable organization tool because they allow you to make changes to the way the query is structured without changing the application at all.

Re:Looking for a good explanation of advanced SQL

[jadavis]

Why are suppliers given direct access to your RDBMS? You should provide a frontend for them that only allows what you want.

Re:Looking for a good explanation of advanced SQL

[jadavis]

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 in the form of a relation that you might not otherwise be able to produce with a query alone. An example might be some kind of query that is hard to express otherwise (if there are lots of bizarre if..then's or loops that are based on the contents of the records), or if you want to make a socket connection in the function and return a table of the current headlines from slashdot.

(2) functions are vital to making a proper user-defined data type: you need the input and output functions, the comparison functions (if you want it to be indexable), etc.

(3) Making a user-defined aggregate function is very useful if you need something other than the basics, and an aggregate function requires several other functions (accumulator, for instance).

(4) Basic batching of statements so you don't have to send so many queries.

(5) if you need to execute several statements and try to recover from various errors in a graceful way, without having to have client-side logic. A simple example might be to make a function that tries to INSERT, and if it gets a duplicate key violation, than it does UPDATE.

(6) triggers require functions also

(7) Stored procedures/functions allow you to express your logic in languages other than SQL, like perl, for instance.

(8) functional indexes are very useful for query optimization.

* Views: In postgresql, views are a rewriting rule that is very useful for being a DBA, because you can optimize a query or change the physical storage without modifying the app at all (or even interrupting it). However, some databases go beyond that and have materialized views, which can improve performance by holding the data seperately, ready to access.

* Triggers: two main types
(1) Constraint triggers: very powerful constraint tool that allows you to check the database for any inconsistencies before committing the transaction. This is very helpful when you have complex business rules, especially when you have several applications accessing the same database. Each application will be expecting the data in a certain way, and if the constraints weren't there, you could have a very elusive bug.

(2) Regular Triggers: I don't find myself using these very often, but sometimes they're necessary. Some have developed very powerful packages, like http://www.slony.info/, which is a very advanced replication system that wouldn't be possible without triggers. Triggers are a very powerful tool, although not used in day-to-day stuff. When you find yourself thinking "I know what I need to do, but I don't know if I can make my database do that", think triggers. Remember since triggers use functions, you can do pretty much anything, like emailing yourself.

-- To find examples and more explanation, the postgresql.org docs are good. If you have more specific questions, email the postgres lists and I'll be there.

Re:Looking for a good explanation of advanced SQL

[Tony-A]

What database feature provides this capability?

The WHERE clause of a SELECT statement.
If you control all the SQL statements, pretty easy.
if the suppliers control (actually any of any of) the SQL statements, difficult.

"the supplier's database account" means that each supplier has his own database?

they were in competition with each other, and we had no doubt that they would do whatever necessary to win the business
You have some very legitimate concerns about SQL injection, like somebody deleteing or messing with competitors' quotes.

Re:Looking for a good explanation of advanced SQL

[Mant]

You could create a set of views with a where clause. Each view will only see one ID and give access to the views, not the whole table.

However, you shouldn't be giving direct database access out like that. Use a front end or interface of some kind and have the code there handle it.

Re:Looking for a good explanation of advanced SQL

[ahmusch]

I know that databases have column level permissions, but what I really needed was *row* permissions -- the supplier's database account would only have permissions to SELECT rows that had a particular supplier_id.

Oracle supports this through a mechanism called Fine-Grained Access Control. Your users, on logging in, can be assigned to one or more Security Policy Groups. These Security Policy Groups can each have one or more predicates for various tables in the application which are automatically applied.

The advantage to this is that even if your users have direct SQL access to your system, a login trigger can accomplish the assignment to a security policy group, and you can create stored procedures which would allow them to change their security policy group (if that's the sort of thing you wanted to do).

So in your example, User X who works for Supplier 100 would be restricted to Supplier 100's data by setting the Security Policy to Supplier = 100. Then every query against the table(s) specified would have the predicate " and supplier_id = 100'. The database would prevent access to any other supplier's data. If user X changes companies and now works for Supplier 101, change him to be a member of a different security policy group, and he can no longer see supplier 100's data.

Oh, you wanted this in MySQL? No idea.

I'd imagine that you could to enforce such a thing through application logic, and as a DBA, if there's one thing I'm disinclined to trust, it's that the application will correctly and completely implement concepts like security and relational integrity. As the database is the point source for data access, it should be the point source for data security and data consistency.

Your mileage may vary, but the list price of Oracle is now significantly more reasonable than in the past (list price of $5k per proc if running on a 2-way machine).

Re:Looking for a good explanation of advanced SQL

[lawpoop]

Sorry, I meant their user account on the database.

A really useful feature for database security is column permissions. So, if I have a user that has no permissions to view or write to a particular column, I feel confident that, even with buggy code, that user will not get data from or into that column.

However, with the quote table, if a supplier with a user account that has select access to that table could theoretically get access to any suppliers quote -- they have access to the 'quote' field. The only thing preventing this is 1. the proper lookup of the supplier_id and 2. the construction of the WHERE clause on the query ( where supplier_id = $supplier_id ). If that process goes wrong, they get to see another supplier's quotes.

Re:Looking for a good explanation of advanced SQL

[lawpoop]

This is probably what I am looking for.

Another MySQL book review?

[smclean]

Oy, here come the SQL flamewars.

Seriously, is there any other reason for these MySQL book reviews? They all sound the same, and who buys books on MySQL optimization anyway? The manual and 'EXPLAIN' should be enough for anyway.

Enough ranting, continue your too-highly-moderated offtopic flamewars.

uhhh, what?

[RelliK]

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?

Re:uhhh, what?

[ajs]

"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?

[Bob Uhl]

...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?

[LordHunter317]

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 say, ain't news, and it most CERTAINLY does not deserve a fresh link from every MySQL article to hit Slashdot.
Perhaps it does, so people think twice about using MySQL. I'm all for the right tool and all, but your argument on why it shouldn't be posted (or why it's wrong) makes no sense (logically or emotionally) whatsoever.

Re:uhhh, what?

[anarxia]

The same site has a similar page for PostgreSQL.

If they were simply trying to discredit MySQL why do they keep updating the site with MySQL versions that fixed each problem?

Software improves through critisism, so in a (weird) way they are providing a service for all MySQL users.

Re:uhhh, what?

[ajs]

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 product Y on slashdot.

Re:uhhh, what?

[ajs]

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 please stop acting as if it's going to poison the water supply.

Re:uhhh, what?

[ajs]

Are we so mainstream in the open source world now that we have bought into the utterly bankrupt idea that every story has two (and only two) sides? Someone who rails against PostgreSQL and MySQL must have a balanced view, and deserves to be linked to from anything that discusses either? Eh, how's that?

Please, it's just a rant. He can rant about all the products he wants, I just don't see why every mention of MySQL has to link to this diatribe.

Re:uhhh, what?

[WWE-TicK]

> MySQL is used by tens if not hundreds of thousands
> of people around the world.

So is Windows.

MySQL == the Windows of SQL DBMSes.

Re:uhhh, what?

[kpharmer]

> 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, Oracle, DB2, Informix, Sybase, etc - so why accept it from MySQL? Does it get some kind of 'get out of jail free' card regarding product quality just because it's open source?

Two years ago the folks from MySQL stated that transactions, views, subselects, etc weren't needed by 99% of the applications out there. That was pure misinformation. And a lot of people called them on it. Now they're getting around to fixing those feature dificiencies in the product. And it's a better product because of it.

Once they get around to fixing all the silent errors and other quality control problems in the product - it'll be a better product for that reason as well.

Anyhow - please don't blame us victims. If you don't want to hear about rediculous problems with the product - help get the vendor to fix them. There's really no excuse after all.

Re:uhhh, what?

[ajs]

"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, etc weren't needed by 99% of the applications out there"

Wrong. Simply wrong. What they said is that their PAYING CUSTOMERS prioritize their work, and while having everything would be nice, they have to do things in the order that puts the bread on the table.

Transactions were a nice little freebie that fell out of BerkelyDB and InnoDB table types, so fairly little work was required. For something like sub-selects, vies, and other goodies, I'd much rather have all of the features of 4.0 and 4.1 before those. Your milage may vary, and you're welcome to use something else. It's just that you don't need to carpet-bomb every article about products you don't care for.

"Anyhow - please don't blame us victims"

Ok, you really need to take a step back and breath... you're not a "victim" of a database. Please.

Re:uhhh, what?

[ajs]

MySQL == the Windows of SQL DBMSes.

Hmmm... Let's see what would describe the Windows of SQL DBMSes... what would be the criteria... Well, it would have to be

• Proprietary
• Attempt to use closed technologies to lock in users
• Add useless, heavy features that impact routine tasks and stability
• Be very expensive
• Have executive management that seeks to dominate the market through manipulation of sales channels

You're right about one thing... there is an RDBMS that fits your description, but I'm afraid it's not MySQL.

Re:uhhh, what?

[Tony-A]

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 distinctions he is making that would side with him on how those distinctions should be made. Having a formal system that is paradox-free is not particularly easy. It is possible to work productively in a system with paradoxes. You just don't push too hard on things like NULLs and thingees that have a mind of their own like TIMESTAMPs and AUTO_INCREMENT.

Insert NULL into a non-null column.
1. The end of the world. Kill the computer.
2. Kill (segfault) the program.
3. Kill the INSERT statement with error message.
4. Perform the non-error parts (rows) of the INSERT statement.
5. Assign the default non-null value to the column without comment.
If you want to preserve the NULL/NOT NULL distinction, you define the columns NULL. Personally I take the extra trouble to define columns NOT NULL precisely to obtain this "erroneous" behavior.

TIMESTAMP very useful if non-standard.

AUTO_INCREMENT primarily a mechanism to allow rows to co-exist which would otherwise collide. Giving explicit values to an AUTO_INCREMENT column is a good way to shoot yourself in the foot.

ENUM of numbers ??? Bad as FORTRAN FOUR = 3
If there is a vital distinction between 1 as an integer and 1 as a string, the design is just asking for trouble.

VARCHAR strips trailing blanks. Design decision. Different world than fixed rigid columns, as in COBOL or dBASE.

Re:uhhh, what?

[LizardKing]

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 configurations supports foreign keys, but the rationale for not including foreign keys support for so long and the laughable workaround is still in the documentation.

When compared to the of disingenous bullshit in the MySQL documentation, the gotchas page looks very restrained in its criticism. A novice using MySQL is likely to be suckered into believing the MySQL mantra that Monty et. al. know better than the rest of the RDBMS industry. Somnething that's proved by the amount of comments in support of MySQL on forums like Slashdot.

Re:uhhh, what?

[kpharmer]

> 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 your definition of erratic for me, because mine doesn't seem to match the example you
> give.

How's this: sometimes mysql statements will produce exceptions consistent with every other major dbms product. And sometimes they won't. There's no discernable patten here. You might just need to:
1. perform *enormous* amounts of testing on your application to determine if database services things you should be able to take for granted really work.
2. read the gotchas page and review your code closely. Then test the hell out of it as well.

Of course, testing is always valuable. But putting an extra month of testing into an application just because the database lack reasonable exception handling is hardly expected in 2004 - or economical.

> Wrong. Simply wrong. What they said is that their PAYING CUSTOMERS prioritize their work, and
> while having everything would be nice, they have to do things in the order that puts the bread on
> the table.

No, I've specifically read interviews with MySQL AB leaders in which they stated that 99% of the applications out there didn't need transactions. Might have been on slashdot, will check later.

> For something like sub-selects, vies, and other goodies, I'd much rather have all of the features
> of 4.0 and 4.1 before those.

Sure, given the list of missing features that they had two years ago, it could be difficult to prioritize. But keep in mind that these relational database features have always been provided in the v1.0 release of every other dbms product out there. DB2 & Oracle supported them *more than 20 years ago*.

An appropriate response from MySQL would be: we'll implement as soon as possible, or we see ourselves as a very simple database. The inappropriate response was: hey, all you new developers - you don't need that stuff anyway. Transactions? Only banking applications need that junk. That was unresponsible misinformation.

Re:uhhh, what?

[ahmusch]

Hmmm... Let's see what would describe the Windows of SQL DBMSes... what would be the criteria... Well, it would have to be
Proprietary
Attempt to use closed technologies to lock in users
Add useless, heavy features that impact routine tasks and stability
Be very expensive
Have executive management that seeks to dominate the market through manipulation of sales channels
You're right about one thing... there is an RDBMS that fits your description, but I'm afraid it's not MySQL.

I'm assuming that is is something of a shot at Oracle, and to some degree it's justified.

However, some of what I perceive you to be calling negatives are not necessarily so --

Proprietary: As RDBMS's are generally considered strategic or enterprise software, and are relied upon by virtually every business not run from a garage to ensure data access, consistency, and recoverability.

Until recently (in business time), Linux (and other open source OS's) were considered toys, not ready for mission-critical applications. People didn't trust it in the data center unless, frankly, they knew they could get someone to talk to in real time if things went bad, and someone they could sue if things got really bad.

However, to my knowledge, there's been no enterprise-class open-source database with a support organization that can be available 24x7 until very recently. Perhaps MySQL will get there -- perhaps it won't. Perhaps someone else will reverse-engineer Oracle, or Sybase, or SQL Server, or DB2, or Informix, and there'll a clean-room implementation that could be used to more directly compete with the established vendors.

Attempt to use closed technologies to lock in users: You call this a bad thing -- I call it coding to the bare metal. Heck, don't things get added to the SQL standard by looking at what's been implemented? People rarely change the RDBMS for a mission-critical system, because there's definite risk of data loss and corruption by migrating the data (also, there's the fact that you tend to need downtime to do so).

Cost: Cost is a relative thing. Mission-critical software could conceivably be free, but mission-critical support never will be, because gurus who can fix your severity 1 problem at 4 am and how can jump on a plane to go to your data center will never be cheap. Accept that as the cost of doing business, because it is.

Useless, heavy features: I don't agree that an RDBMS should be an a la carte roll-your-own system. It's a system -- there's meaning to the word -- and the interdependencies required for a smaller code base can get nasty. Besides, I know with Oracle lots of features are optional, and don't have to be purchased, and some you get for free don't need to be installed.

Management: This is slashdot. Name a technology company executive (or any sales organization) who is not reviled. Larry Ellison is a whole pile of neuroses and wants to be King of the World, but at least he's not trying to get there primarily through litigation (DoJ suit regarding PeopleSoft merger was a response to DoJ's recommendation, not a stickup) or through extension of a de facto monopoly

I, personally, feel your comments are more damning to SQL Server (pick you OS, so long as it's ours) and DB2 (3 RDMBS, all with the same name, all who do things differently depending on the OS). None are perfect -- if one were, we'd all use it, and then it'd be broken into several companies all of which would go their own direction.

Re:uhhh, what?

[Anonymous Coward]

3 is the only correct answer--make the developer decide what should happen. Irreversibly executing a request that didn't even make sense is stunningly reckless.

There's an argument that that 1 and '1' aren't really that different, but 0 and 'foo' shouldn't ever be silently interchangeable.

Re:uhhh, what?

[Tony-A]

Irreversibly executing a request that didn't even make sense is stunningly reckless.

There is a difference between your inability to make sense of something and the thing itself not making sense.

INSERT INTO foo SELECT * FROM bar LEFT JOIN baz ON whatever.
You are saying which?
That defining columns in foo as NON NULL is stunningly reckless?
That inserting rows from a join is stunningly reckless?

make the developer decide what should happen.
Which developer.
Seems the developers of MySQL did in fact decide what should happen.

Re:uhhh, what?

[Tony-A]

and I honestly do not understand what is so damned scary about MySQL.
Send query, get answer. Life good. Carry on.

I've had this rattling around in my head last couple days. Maybe this sheds some light on the matter.

You have some hundred thousand records of raw data and an hour in which to process it.
MySQL: "Send query, get answer. Life good. Carry on."
Pedants: will still be working on it a week later.
Now, if you have "enterprise" class requirements, which do you use if you are responsible for the complete results.

MySQL is atomic at the statement or query level. This is a different granularity than something which is atomic at the transaction level. I suspect that the paradigm of the transactions, triggers, cascading deletes, etc. is that one expects to start a transaction, delete something at a high level, have the delete cascade until it runs into something which must be deleted and must not be deleted and causes a roll-back of the transaction. Dunno about you but to me that looks like an accident looking for a place to happen.

MySQL is extremely predictable and conservative in use of resources and if you're brave and daring enough, can be pushed beyond reason. I haven't done it but I'm sure than someone has managed to process at the "same time" raw data which amounts to about 250% of total disk space. Run it out of disk space and MySQL patiently waits for space to be freed without any other sign of a problem. There are surprises, but there are good and sound reasons for those surprises.

Available in 4.1

[Anonymous Coward]

Actually that's not true, subqueries are available in 4.1 They should have been there all along, but hey you can use them now, so no worries.

Re:Available in 4.1

[superdifficult]

Boo to the Mod who gave this a Troll. This is a simple and true statement. Plus anyone who says 'no worries' is obviously polite ;)

Not from O'Reilly

[bvankuik]

Nice review, but if the book is not from O'Reilly, I probably won't buy it.

Re:Not from O'Reilly

[M. Silver]

I'm of mixed feelings on that. I have the obligatory bookshelf dedicated to books with woodcuts on the cover, but I'm not happy with some of the ones I've gotten recently (I won't say "recent ones" since I think the Postfix book, at least, isn't all that recent). The New Riders books I have (both Paul duBois, I believe) I'm pretty happy with.

I'd not trust Apress until I hear the whole story on this issue and see how it settles out, though. Probably fine, but...

Re:Not from O'Reilly

[davegaramond]

Your loss then, I guess. True, O'Reilly churns out many good books, but of all my favorite books, only about 10-20% of those are O'Reilly's. SQL For Smarties, The Data Warehouse Lifecyle Toolkit, Unix Haters' Handbook, ...

You're missing _a lot_ if you limit yourself to O'Reilly.

Re:Not from O'Reilly

[Anonymous Coward]

Nice review, but if the book is not from O'Reilly, I probably won't buy it.

That was true ... 8 years ago. Until recently it was a reason not to buy a book. The last MySQL one from them sucked. Their Ant book was pretty much a printed version of the website.

I've been impressed with Manning and some of the Wrox books.

Re:Not from O'Reilly

[LizardKing]

Nice review, but if the book is not from O'Reilly, I probably won't buy it.

The O'Reilly book on MySQL is a piss poor rehash of the documentation available on the web, and a subset at that. If you apply your "O'Reilly only" attitude to all your computer book purchases then I hope I never waste my time interviewing you for a job at my company.

Chapter one...

[gik]

...Install PostgreSQL

Re:Chapter one...

[Anonymous Coward]

Or Firebird.

article NOT A RANT, those are legitimate gotchas!

[Anonymous Coward]

It's unfortunate that your comment got modded up, since it makes a claim that isn't true.

I use MySQL in maybe 60% of my projects, and I have been bitten by a majority of those issues. MySQL has some serious problems as an SQL-compliant database (let alone as a *relational* database), and that site simply points them out in an objective manner. In fact he says they aren't bugs or mistakes, but *documented, yet unexpected, behavior*.

I would recommend anyone who is using MySQL (by choice or otherwise) to read and understand that article (and keep in mind some of those problems have been fixed, which he points out).

Also, as a general comment about the closing sentence of your post, have you considered that some pieces of software might be better than others? Or one piece of software might adhere more closely to a standard than another? These are all topics worth discussion.

Mike Hillyer

[Anonymous Coward]

LOL. He also claims to be a "VB expert". Sigh...slashdolt, news for newbies. Move along, nothing to see for us real experts who don't fuck with crap like mysql or vb.

Genuine question: MySQL not for mission critical?

[kanweg]

I have started a company and am in the process of putting my precious mission critical data in a MySQL database. RAID 1 is used to prevent loss of data, and apart from that DVDs will be burnt using the superdrive.

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

hi this is jenna jameson

[Anonymous Coward]

oops I actually stumbled on your *cute* webpage and this sql stuff has gotten me sooo hottttt....

Re:article NOT A RANT, those are legitimate gotcha

[Bingo Foo]

Also, as a general comment about the closing sentence of your post, have you considered that some pieces of software might be better than others? Or one piece of software might adhere more closely to a standard than another? These are all topics worth discussion.

How true. For instance, emacs is a much better piece of software than vi.

I didn't think that MySQL was designed

[Anonymous Coward]

I just assumed that it congealed in a gutter somewhere.

does not compute?

[Anonymous Coward]

Experienced users will find quite a lot of valuable information that will extend their existing knowledge base.

Experienced users.. MySQL .. Experienced users... MySQL .. man, talk about cognitive dissonance!!

I guess some experienced users are stuck *maintaining* MySQL databases so maybe that's what he's talking about..

A book review about PHP and Pear DB

[Linuxathome]

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?

Re:A book review about PHP and Pear DB

[Anonymous Coward]

How often do you switch database backends? How come people talk about this like it's important? I've *never* moved a database from MySQL to Postgres or vice-versa. Yes, sometimes I change the tables but there's no way to encapsulate that in *one* place (however it's good to keep that stuff together in your code so it's easy to change). But you don't need to be "SQL agnostic" whatever that means (I thought SQL was a standard).

And if you're choosing between MySQL and Postgres, why would you even consider MySQL??

Re:A book review about PHP and Pear DB

[Penguin]

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 switches between MySQL 4.0 and 4.1.

Re:A book review about PHP and Pear DB

[Canberra Bob]

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.

Re:A book review about PHP and Pear DB

[jadavis]

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 and unweildly, and distracts from the purpose of the application. If one of your supported DBs doesn't support proper constraints, that means that every piece of your app has to maintain and check the consistency of the data in the database in excrutiating detail. If it doesn't, than it could create bugs very difficult to find, and create inconsistencies in the DB that you have to fix manually.

I've heard of a large application that tried to be DB agnostic (to attract more customers) and just the consistency maintaining/checking code was as many lines as the rest of the application, and caused major performance problems.

So, it's a tradeoff (like everything else, I suppose): are those extra users who use the DB that brings the lowest-common denominator down 5 notches worth the client-side work? Maybe. Just make sure you know what you're getting into. It seems easy at first, but as the application grows, your ultimate responsibility (if making a high-quality application) will be the client-side reimplmentation of all the features in the DB that you need but can't use because some other DB on your "supported platforms" list doesn't offer the feature. That's a big price for a "We support Foo RDBMS" checkmark on the product page.

And really... is that what you want to spend your time debugging? The same stuff that DB projects have spent many years debugging already? And then asking the clients to buy more hardware to support the performance-hog client-side database layer is not going to go over very well.

Re:A book review about PHP and Pear DB

[sean@thingsihate.org]

Maybe you want to write something and release it, and leave the choice of database up to the user. If I was already running Postgres, and downloaded some PHP thing you made which only worked with mysql, I am probably going to decide that I'd rather not use your product than install yet a second database server just for it.

If your product worked with my database, whatever it was, I wouldn't throw it out right away.

Re:A book review about PHP and Pear DB

[ahmusch]

I completely agree. SQL agnosticism is orthogonal to performance without some truly torturous coding, if not complete code forking.

Different databases handle all sorts of thing differently, like:

Transaction consisentency
Locking (specifically, granularity and escalation)
Logging
Undo mechanism
RI mechanisms (triggers, constraints, deferrability, reliance)

Some databases (DB2) are designed for lots of small, short, targeted transactions. Some databases (Oracle) are designed for large, long, complicated transactions. Some databases don't seem to understand the concepts of transactions, at least not from an ACID perspective.

The only time you should concern yourself with database/SQL agnosticism is if you are working on a COTS product, as I am. If you're working on a hobby or internal project, utilize the full feature set of the database! You wouldn't not fully use $SCRIPTING_LANGUAGE because $SCRIPTING_ALTERNATE didn't support a feature cleanly. How often does a production app get migrated to a new OS architecture, like from Unix to Windows, or Risc to X86? Not often. But we pretend that we're going to potentially migrate an app from $RDBMS to $RDBMS, and I've never seen such a process work.

Most people pay big money for a sophisticated piece of software, and pretend it's nothing more than a file system or VSAM, and that's a colossal waste of time, money, and people.

Hmmm...

[Anonymous Coward]

INSERT INTO mysql_sucks (comment) VALUES ('MySQL isn't a real database so this book makes no sense');

Are you sure MySQL could handle such a complicated procedure?

Thanks for setting me straight!

[Anonymous Coward]

MySQL runs on Windows

Postgres it is then. Thanks for the tip.

Re:Thanks for setting me straight!

[Anonymous Coward]

You're an idiot.

Re:article NOT A RANT, those are legitimate gotcha

[Anonymous Coward]

Okay, why?

In the database world, there is a solid, underlying foundation: relational theory. This was developed, what, 30 years ago? It has *provable* characteristics.

So when you want a database, pick the one that has the most of those characteristics. Unfortunately, most folks seem to think it's Emacs vs. vi. Just pick the one that "feels" better, or that they learned first. That's fine for text editors, but not, say, for the foundation of a mission-critical finance system.

Re:article NOT A RANT, those are legitimate gotcha

[FuzzyBad-Mofo]

And Vim is better than either..

Re:article NOT A RANT, those are legitimate gotcha

[Anonymous Coward]

You make a good point. From now on I will not program in C++ until I find an open source compiler that respects the export keyword.

Re:Genuine question: MySQL not for mission critica

[Anonymous Coward]

I've used MySQL for years in very high volume, large scale, distributed "mission critical" applications (if you call running your whole company "mission critical"). Not once has the database failed to perform it's job. Networking, Hard Disks, Memory, etc... They all failed. It's funny to read all the self described "experts" on here rip the product when the list of household names successfully and reliably using it on a daily basis is long and distinguished (like my johnson).

FireBird Rocks

[Anonymous Coward]

I've found FireBird to be an excellent choice for better ANSI SQL compliance, support for ACID transactions, single-file database portable across platforms, etc. It's also one of the easiest to administer that I've come across. I like that the embedded version is a single shared libary that implements the client API. I've not used it for a truly large database. I've used DB2, Oracle, SQL Server (the only database I've used that became corrupted and lost production data) and Informix.
As for MySQL, the lack of transactions and poor support for SQL has put me off it for real projects (no subqueries--life's too short).

Re:FireBird Rocks

[Anonymous Coward]

You forgot to mention that it's open source via MPL, which allows you to use it even in commercial apps. Very flexible.

Re:FireBird Rocks

[davegaramond]

Why do people keep saying MySQL doesn't have transactions or subqueries? For heaven's sake, please update your 4-year-old facts!

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.

Re:FireBird Rocks

[Anonymous Coward]

The stable version does NOT have subqueries. Version 4.1 does, but that is still beta quality software.
The transaction support in 4.0 is horrible and slow. Rollbacks are O(n) operations (for InnoDB, that is) etc...

Re:FireBird Rocks

[Anonymous Coward]

> The stable version does NOT have subqueries.

Wrong.

> Version 4.1 does, but that is still beta quality software.

Wrong.

Please check the site before posting false statements like this. MySQL 4.1.7 was released in October and is GA status (production/stable).

http://dev.mysql.com/downloads/mysql/4.1.html

Database design?

[Tablizer]

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.

Re:Database design?

[ahmusch]

Database design should be a generic RDBMS book for the most part.

I wish I could explain just how incorrect and incomplete your statement is.

A few questions to demonstrate:

You've got a financial application where you want to see account balances as they change in real time. How do you do it so that performance of both update (for the transactions coming in) and select (for the users of the application) performance will scale linearly if not logarithmically as the number of transactions increases? For which RDBMS?

You've got a table with CHAR, VARCHAR, NUMBER, and DATE columns. Some columns are updated often, some are updated never. What should the physical order of the column be, and why? For which RDBMS?

What's the impact of table partitioning for various types of partitioning, such as hash (round robin of rows), list (specific discrete values correspond to different partitions), and range (values between x and y-1 are in one partition, values between y and z-1 are in another partition). How does it impact performance? How does it impact scalability? How does it impact manageability? Which vendors support which partitioning schemes? Why does it matter how you tend to access your data important when determining how to partition? How can partitioning inhibit performance?

What's the impact of issuing an update versus issuing a delete and then an insert?

What's the impact of using triggers to enforce RI versus using database constraints? Which systems support which methods?

If you want the generic theory of database design, read Codd. If you're not interested in scalability or integrity, use Access or MySQL, and you'll be fine.

If you want to build mission-critical systems, the choice of platform must direct your design decisions, and if you ignore that, you will paint yourself into a corner.

You don't need vendor-specific tips -- you need vendor-specific books, such as Tom Kyte's _Effective Oracle by Design_, or Yevich and Lawson's _DB2 High Performance Design and Tuning_.

Re:Database design?

[Tablizer]

What you are describing is "performance tuning", not database design, at least not how I interpreted it. I am talking about well-factored schema design such that the information is not redundant, describes the domain well, and is flexible to future needs. (Some say that schema design should be dictated by performance needs rather than logical normalization. But this is a contraversial topic that cannot be settled here.)

If you want the generic theory of database design, read Codd.

Codd might have been smart, but he was not the best writer in town.

Re:Database design?

[ahmusch]

A database that is perfectly "well-factored", lacking in redundancy and providing complete consistency -- but runs like a dead pig in molasses is not a well-designed database, because it does not meet the often implicit requirement to perform at a given throughput.

I say that if you do database design without considering the performance impacts, you're not doing database design in the real world. At that point, it's an academic problem. If you're attempting to solve an actual problem or build an actual system, you must consider the performance nature and limitations of the database every bit as much as a civil or structural engineer must understand the difference between suspension and piling bridges.

The very fact that you refer to domains means you're somewhat familiar with logical modeling, but the difference between logical modeling and physical modeling is significantly greater than switching the view in ERwin. Hell, the flexibility toward future needs is an implementation specific issue, because with some RDBMS's you can add, remove, and change columns under some circumstances, and in others you cannot.

Regrettably, academia does not focus on these differences, so we tend to get perfectly elegant designs -- that don't work, and it's all the DB designer's fault. Better tools that take into account the questions I raised to make design more interactive may help, but often those questions aren't and can't be addressed before a system runs into customers.

Codd's terse, but the theory is complete. It's written in an academic style with the assumption of a great many fundamentals.

Still needs lots of work

[gtoomey]

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.

Re:Still needs lots of work

[Klync]

I could look this up, but you could to, so I won't. ;-) Iirc, postgres was still randomly deleting data when mysql was feature-bare but stable. It got adopted. postgres has been slowly climbing in adoption ever since, but still lagging.

If I'm writing a php app for some guy who's going to put it on a vhosted box, they're likely going to have MSSQL *shudder* or mysql. If I'm just picking up php on a need-to-know basis, I'm not going to bother even setting up postgres in my test suite at home; and the vicious cycle continues.

That said, mysql is a very savvy company, but they seem to not be resting on the early adoption - they are constantly putting out a new, better project, and I can only imagine what kind of benefits their paid service could provide to the right project. mysql is no vhs-cabal. Betamax got buried under the brooklyn bridge.

Re:Still needs lots of work

[InfoCynic]

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, you're absolutely right, although not a deal-breaker. We got by with temporary tables for a long time, and now we can use subqueries to do the same thing.

Views are on the 5.0 agenda I know, not so sure about synonyms, but I'll not try to argue that you can do everything with MySQL that you could with Postgres. I think what it boils down to is that very many of the common applications of MySQL out there simply do not NEED the extra functionality these tools provide, so it's a fine choice.

Re:Still needs lots of work

[cant_get_a_good_nick]

I'm using 4.1 (its not a production release yet)...
MySQL 4.1 series marked as stable

Re:Still needs lots of work

[jadavis]

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 security reasons (remove quotes and backslashes and whatnot). Even if it's a radio button, that wouldn't slow me down from sending your website invalid data. Check it every time.

The constraints are important because if you have several parts to an application, or perhaps several applications all accessing the same data, you don't want one part to adversely affect another. If one part of the app tries to insert invalid data that would confuse another part of the application, the constraint will prevent the insertion and raise an error right there. Then you very quickly know where the bug is. If you had no constraint, the bug could appear 3 days later in some report of sales data, and you'd have no idea where to find the bug.

Constraints have saved me a LOT of time bug-hunting.

Re:Still needs lots of work

[Anonymous Coward]

"When I find out why VHS became more popular than technically superior Betamax"

Jesus... It's been over 20 years and people STILL don't know the answer to this question? Here, I'll give you the answer:

During the important VHS vs. Beta years, VHS could hold a 2 hour movie on one tape. You had to switch tapes with Beta. That's unacceptable to a LOT of people and still is (nobody likes 'flipper' DVDs).

The image quality wasn't a big difference. Most people had shit TVs anyways and were happy enough to get the damn technology any way they could.

Betas were more expensive.

Porn industry went VHS. Popularity took off and more regular movies became available on VHS and not Beta. Beta died slow death after this.

End of story.

Re:Still needs lots of work

[MikeBabcock]

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.

Re:Still needs lots of work

[alien at large]

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

Pornography actually, but how that translates to mysql being more popular than postgres is your problem :)

Re:Still needs lots of work

[JavaRob]

I meta-modded your post as interesting, because in some applications it's true (and it's essential not to use subselects as a crutch if you don't understand the different joins), but overall I have to disagree -- there are just too many applications where I couldn't even consider MySql (3.x) because of the lack of subqueries. Any kind of reporting is damned hard without subqueries, especially if you want to reuse common subqueries in your code when you're building multiple reports with some common parts. Sure, you can use temporary tables... but you can't keep them, because you're reporting on changing data, and every report is requested with different parameters....

Even worse in my opinion, just opening a console to quickly get some data out of the database is much, much harder without subqueries. I have a few simple web applications running against MySql 3.x (trying to get my host to upgrade...), and I always start cursing when I want to do something like list the accounts with more than 100 orders, or something like that (fake simple example; I'm not that successful).

select account_id, the_count
from (
select count(order_id)
from order
group by account_id
) as sub
where the_count > 100

There's no simple, one-step way to do that w/o the subquery that comes to mind... and if you need to add a group-by on the upper level (like if you want to also display the last-used shipping address) you're really hosed.

Re:Still needs lots of work

[MikeBabcock]

I agree -- subqueries make quick and dirty queries easier. But that's exactly my point -- efficiency often sides with no subquery usage when not necessary.

Even in your reports example, I would disagree. During the running of reports, my customers want everything to add up. They don't want the subquery that chose the list of customer accounts to report on to change between the AR balance report and the GL report. As a result, the temporary table solution is perfect.

Live reporting of database information is a different story (reports were probably a bad example). If you want second-by-second data of several types live from a dataset, you'll probably find subqueries very useful.

That said, you'll probably be running Teradata and not need them anyway.

Re:Still needs lots of work

[JavaRob]

Quick background -- my main current project is continued develoment on a web-based order entry system for a mid-sized home/garden store chain. Using IBM DB2 for them. I also have a few independant, much smaller online projects (here's the main one), that are using Java/MySql or PHP/MySql.

Live reporting of database information is a different story (reports were probably a bad example). If you want second-by-second data of several types live from a dataset, you'll probably find subqueries very useful.

My experience with reports may not really be the standard one -- I've been working so far always with live reports, hitting live data. I've used temporary tables while doing data-mining-by-hand kind of work, but never in one of the online-available reports.

In the home/garden project I have a report that starts on sales by store in a date range, and you can drill down into the data to see departments within the store, then items within the department; I just have to peel off another layer of subselects on each level of the report, and always apply the date range/dept/store filters on the lowest level, so it doesn't need to work with any more data than necessary. That'd be ugly without subsels.

Kind of a moot point in that case, to be fair, since we aren't considering MySql for that project, but I get into scrapes all the time in my smaller projects just because I forget I don't have multiple levels of group-by... or I just want to use a subselect because it's cleaner, shorter, and easier to maintain, despite being slightly less efficient (which doesn't bother me with a fairly small dataset, especially if I'm smart about limiting the data in the subselect, instead of in the parent select).

BTW -- do you only use temp tables in cases where they're reusable, or is it reasonable to create a one-off temp table for something on the fly? I do that when I have to when I'm doing work by hand, but not yet from an application.

They don't want the subquery that chose the list of customer accounts to report on to change between the AR balance report and the GL report. As a result, the temporary table solution is perfect.

Here you're talking about tables that are probably just repopulated daily, right? That's a great idea... I get jumpy about maintaining totals tables, that sort of thing (it's kind of anti-normalization, and can be hard to maintain w/ triggers or in the app), but just taking the most useful cut(s) of the data and putting it into one big table every night could be very useful. I've been assuming that data a day stale wouldn't work for my customer, but it's worth asking. :)

Re:Still needs lots of work

[MikeBabcock]

In the case cited at the end, I'm dealing only with "within the timeframe of generating the reports in question" which might be month-end statements for customers or doing their balance sheets or running electronic X/Z tapes.

If I'm going to keep non-normalized data around, I do it in temporary but named physical tables, not "temporary tables" (excuse the nomenclature issues). Perhaps:

CREATE TABLE ActiveAccounts20040510 SELECT ... FROM ... LEFT JOIN ... LEFT JOIN ... ORDER BY ...

Then I can select against that data and my table name is programmatically obvious elsewhere in the program. I do this less and less now that MySQL supports query caching though (doing the same select twice on the same data will return the results from memory without touching the tables, indexes, etc.).

Granted, there are things I prefer about Oracle, but also about the visual appeal of MS Access or the old report generator in Borland Paradox (which I have two boxed copies of at home). In some cases, I'd much rather work with flat files or cdb files than SQL at all of course. I don't believe in the one tool for every job system either.

if it's really critical...

[SethJohnson]

..for the foundation of a mission-critical finance system.

In such a scenario, I'd have to say mySQL isn't really there. MySQL is a terrific solution for all kinds of web publishing / application needs. But if finances are at stake, you gotta go with a product where you can sue someone over stuff that goes wrong.

Re:Genuine question: MySQL not for mission critica

[Anonymous Coward]

Bert-
Honestly, use PostgreSQL if you need a SQL database. There are a ton of reasons not to use MySQL, especially for M.C. data. I ship a product that supports nearly every db known to man (PostgreSQL, DB2, SQL Server (MS & Sybase), Sybase SQL Anywhere, Oracle, NCR Teradata, Informix, SAP DB) but we will not support MySQL anytime soon. Data corruption problems, lack of referential integrity, no triggers, no views, no synonyms, no stored procedures, these aren't things that are optional for a modern database. If you don't need any of that, do yourself a favor and use Berkley DB.

mysql users

[TheLibero]

after few hours of the original post, nobody come up with useful discussion!

No wonder I can't find useful answers for mysql questions!

OT: Reply to sig

[Klync]

... more like a boxer playing chess

Re:OT: Reply to sig

[Tablizer]

... more like a boxer playing chess

You should see what Mike Tyson did to my bishop.

Neutral? Yeah, right.

[Spy der Mann]

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 functions? No thanks. Named locks? logic functions inside queries? etc etc...

I use phplib DB_SQL to keep layers separation. Frankly, if you want to be 100% "neutral" you would end up using only the most basic features of SQL (select... from... where. ta-da!), and that's useless.

Let the other guys handle SQL adaptation when the business decides to adopt another implementation. Still, if you code multi-tier you won't have much problem there. I see multi-tier programming having many more advantages than simply maintaining SQL neutrality.

Subqueries vs. joins

[Spy der Mann]

Yes, subqueries make the programmer's life a LOT easier. But IMHO using joins works in most cases. There's a little problem, it requires a perfect understanding of how they work, or you'd get in trouble. Also, I've read that joins is generally more efficient than doing subqueries.

Oh yes, I'm using MySQL 4.0x and it has unions, too.

If everything else fails, use temporary tables.

Re:Subqueries vs. joins

[ahmusch]

Also, I've read that joins is generally more efficient than doing subqueries.

It depends on how restrictive the subquery is -- for example, if a subquery of a 1,000,000 row table returns 10 rows and you're joining it to another 1,000,000 row table (or to itself), then the subquery will likely be more efficient than a simple join, especially if the order in which the predicates are evaluated is tricky -- is the join predicate evaluated before the data restriction predicate?

If everything else fails, use temporary tables.

Provided, of course, that you've got the disk space, fast enough disk, and things like concurrency and data integrity are of secondary importance.

Ever heard of InnoDB?

[Spy der Mann]

Seems to me as if people always thought of MySQL 3.23. InnoDB is supplied with MySQL 4, and it's A.C.I.D. compliant.

Problem is, very few people use it because they don't know it (InnoDB is relatively new, 2 years ago we didn't have MySQL 4).

125th post

[mollymoo]

yo dadda sucks my ass

Re:125th post

[mollymoo]

Isn't it annoying when your pissed-up (that's drunk to those of you unfamiliar with English slang) friends post shit using your account?

MySQL is good

[tjstork]

MySQL is popular because it is good. It's simple to install, the C client is probably the most straightforward in an RDBMS, it's reliable, and it performs well enough for many small to midsize web sites. Is it an Oracle? Well, no, but, then again, not everyone needs a 400hp car and not everyone needs a 20k database server!

MySQL

[joxeanpiti]

Can I use this database as a replacement for large databases, such as Oracle Databases?

My case is the other way around

[hppacito]

I do understand stored procedures, views and triggers, but I don't understand foreign keys !... may be I missing some chapters in the book !

VHS

[Anonymous Coward]

Betamax had higher image quality, but paid the price by having shorter run time on each tape. VHS became more popular because it's longer two hour run time allowed a user to fit an entire movie on the tape.

I think this is directly applicable to the MySQL vs. PostgreSQL debate because for a very long time MySQL has been available and easy to use on Windows and on other platforms. PostgreSQL has not been available on Windows except through the use of Cygwin with problems.

In both cases its about fitting user's (especially novice users) needs.

Erratic:

[warrax_666]

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 this thread (by responing to these so-called "anti-MySQL" people as you like to label them) is you.

I, for one, am happy that I know about that page in case I ever decide to deploy MySQL for anything. Much of the documented behavior is downright scary and absolutely necessary to know about if you care about correctness at all.

Re:Erratic:

[ajs]

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 put too much effort into this, and no one really cares anyway besides folks like you that feel so strongly that over the course of this thread, you actually marked me as a foe.

Re:Erratic:

[warrax_666]

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.

Lucky.

[Anonymous Coward]

It has always done what I want it to do faithfully.

So, you've been lucky to not hit those cases or you just haven't been doing enough testing to notice the problems. Even so: One anecdote does not equal universal truth, dumbass.

Re:Genuine question: MySQL not for mission critica

[Mant]

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:Genuine question: MySQL not for mission critica

[Anonymous Coward]

Grandparent: and apart from that DVDs will be burnt using the superdrive.

Parent: 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.

Sounds like you have two routes to your data right there, whatever application does the updating *and* mysql_dump (or whatever it is called that does the backups) Ask yourself, is there any moment in my application I do not want a backup to be made? The correct use of transactions should ensure there is not. Running backups at night is a proven unseccesfull sollution to this ;-) Just a side note, your application threatsafty should be perfect to trust you application for data integrity control. It is just as close to perfect as your database system code right?

Postgresql isn`t the only free as in speech alternative, there is sap-db as well which even has a lot of oracle compatibility features.

A few words from one of the authors.

[Zontar The Mindless]

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. :)

Re:A few words from one of the authors.

[Anonymous Coward]

Working cluster? ... ok, I was ready to rant about the read-only replication "clusters" you guys were on about a year ago, but I did RTFM.

MySQL clusters are memory-only database clusters. They suffer from a few weaknesses in the expected features of a database...

"Max number of meta data objects is limited to 1600 (includes tables, system tables, indexes and BLOBs)
Max number of attributes per table limited to 128
Max row size of 8k (not including BLOBs)
Max number of attributes in a key is 32 "

"Foreign key construct is ignored (same behavior as MyISAM)"

"Only support for READ_COMMITTED isolation level (InnoDB supports READ_COMMITTED, REPEATABLE_READ and SERIALIZABLE)
No disk durable commit (are replicated, but no guarantee that logs are flushed to disk at commit)" [sic]

"Alter table not fully blocking when running multiple MySQL servers (no distributed table lock)
MySQL Replication will not work right off if updates are done on several MySQL servers. If a partitioning scheme of the database is done at the application level, and no transactions takes place across these partitions, it will work. "

So let me get this straight. I have a limited number of objects, all of which must fit in RAM, ACID can't be guaranteed nor can integrity constraints, and if my cluster UPS goes out committed transactions will be lost.

What you have is a science experiment, a milepost in the right direction.

Call me back when you have a working cluster. I'd really like to see it happen, and some of the parts are there, but it's not ready for production until I get ACID, disk persistence and all the InnoDB features (I'll argue that any other kind of table store isn't a proper DBMS anyhow).

Re:Genuine question: MySQL not for mission critica

[Anonymous Coward]

Make sure you don't take that backup while anyone is using the database. The lack of ACID means that you could back up half an update and make your backup useless.

(Also make sure you don't try to do this as a live filesystem backup, regardless of what DB you're using, unless they have a system to allow for live filesystem backups. Most DBs keep the recent transactions cached in memory instead of being written to the drive immediately. Use the appropriate dump tools and backup the dump.

Re:article NOT A RANT, those are legitimate gotcha

[ajs]

"I use MySQL in maybe 60% of my projects, and I have been bitten by a majority of those issues"

Ok, look someone's not reading here... I'll make this one last post, but I really have to stop this thread.

I never said that the items listed were incorrect. They would not work as rhetoric if they were lies. However, the page is designed, not to help (all of the items listed are in the documentation on the MySQL site), but to discourage the use of the database. Any database can be ranted about, but for some unknown reason people choose to rant about MySQL quite a lot.

I think part of this is that PostgreSQL as a project has felt that their time and effort on full ANSI compliance was not rewarded, and so a lot of specifically PostgreSQL folks want to rail on about how MySQL did not make such an attempt, but instead focused on non-standard features like their full-text searching.

That's fine, but let's not confuse those disagreements for any actual problems with either product.

MySQL has some serious problems as an SQL-compliant database (let alone as a *relational* database)

You got those backwards. SQL compliance implies that you are a relational (e.g. set theory applied to a table structure) database. MySQL might be non ANSI SQL 9X compliant, but that does not make it a non-relational database. There are ANSI SQL features which MySQL simply does not support.

and that site simply points them out in an objective manner. In fact he says they aren't bugs or mistakes, but *documented, yet unexpected, behavior*.

It is most certainly not objective, and if you really think that it is, then I don't think we can have this conversation rationally.

Ok, that's it. Thanks guys, but I really do need to go get on with my life. If my comments here have been helpful, great, if not... well, good luck with your work anyway.

Re:article NOT A RANT, those are legitimate gotcha

[Kardamon]

I second this. Also see here.

Re:Genuine question: MySQL not for mission critica

[MikeBabcock]

You MySQL haters do realize that we're not just talking about MySQL version 1.0 anymore, right? Linux 1.0 kinda sucked too.

MySQL has binary logging of queries, the InnoDB back-end supports referential integrity as well as consistency (at least in my tests).

Perhaps you didn't read the MySQL manual and shut off autocommit before testing? Or did you actually test it before spouting off?

BEGIN;
SELECT Amount from Account1 where ID = Foo;
UPDATE Account2 SET Amount = Amount + Bar WHERE ID=Foo2;
-- spawn new process --
SELECT Amount from Account2 where ID = Foo2;
# returns original amount or blocks
-- back in original process --
UPDATE Account1 SET Amount = Amount - Bar WHERE ID = Foo;
COMMIT;
-- spawn new process --
SELECT Amount from Account2 WHERE ID = Foo2;
SELECT Amount from Account WHERE ID = Foo;

Both return the right values. Do it yourself.

You pulled the plug half-way through? Big deal, still good.

Its called active development.

Re:MySQL table still huge after deleting most reco

[exhilaration]

Thank you very much, you just saved me 25 MB.

MySQL (was: Re:FireBird Rocks)

[catenos]

The stable version does NOT have subqueries. Version 4.1 does, but that is still beta quality software.

That not up-to-date. 4.1 isn't considered beta since 2004-06-24 and had it's first production release over a month ago, on 2004-10-23. See here

The transaction support in 4.0 is horrible and slow. Rollbacks are O(n) operations (for InnoDB, that is) etc...

Slow rollbacks are not really a problem, if you don't abuse them. That is, don't write your transactions with failure as an expectation (like inserting a row and waiting if it fails due to a unique key), but do your own checks (most often you don't need additional queries, because you already have the data or can expand an existing query). Not only does that make your queries faster with most databases (even those with good rollback support), but also makes your application more robust (in case of db changes)

Re:Genuine question: MySQL not for mission critica

[mw]

Since InnoDB is marked stabled since Feb 2002 and you use it "for years" I doubt you're using transactions.

You should not rely on a non-ACID DB in a mission critical environment. You might have luck for several years - but there's no guarantee.

And there are a lot of ways where MySQL will fail miserably in case of failure, here's just a simple example:

update balances
set balance=balance-1000.0
where id=some-id;

Now your NIC fails (or your router or whatever)

now your application tries to book that to another customer:
update balances
set balance=balance+1000.0
where id=some-other-id;

Oops, that failed. But where's the money gone? It vanished....

OpenOffice targeting MS Access

[mikefe]

It looks like Open Office is targeting MS Access in their next release.

I have been testing the 1.9.mXX releases (currently preparing for their first beta release) for a few weeks now, and most issues have been minor formatting changes, and a couple crashes here and there -- all of which have been reported. The one major issue I have come across is the problem where graphics are lost after saving changes to a document Issue 36459 where the fix has already been checked into 1.9.m64.

The pre-beta milestones have improved import/export filters, and many new features most people will like. It needs people to stomp on it in their worst way (ie, regular usage :). Does Open Office 1.9.mXX render a document differently than your other office suite (be it Word, Wordperfect, Works or etc)? Do your part:

o Create a seperate file with only the portion that changed from the other suite.
o Create a PDF of the document from the other suite.
o Create a PDF of the document from the latest development snapshot of OpenOffice (using the same pdf program if possible -- I use the Open Source PDFCreator which is a ghostscript front end).
o Zip the files (I use the Open Source 7-zip program)
o Report the issue (and create an account if you don't have one) with your best description of the problem with a step by step list of what actions need to be performed to reproduce the problem.
o Attach the zip file of the three files you created before.

The development code needs more people who are able to write bug reports -- that means don't install it on grandma's computer. If you think "it doesn't work" is a good bug report then please move to the next message, thanks.

Suck/NoSuck dichotomy

[fm6]

I don't much care for your adage. The patent files are full of useless inventions that are not the right tool for any conceivable job.

Still, you sort of have a point. There are plenty of applications that work well within MySQL's limitations. Then again, they also work well with other low-end DBMSs, like FireBird/Interbase and PosgreSQL. (Let's leave Oracle out of the discussion -- you don't use that kind of software unless you have deep pockets and serious support infrastructure.) Now, if you know for sure that your application will never evolve beyond MySQL's limitations, I suppose there's no harm in using it. But most application do evolve.

I don't usually care for the usual "It sucks!" versus "It rules!" debates you see on Slashdot. Everything has its strengths and weaknesses. But in this case I have to say that MySQL does suck. Not in the sense that it's totally useless (obviously not, since so many people use it). But it does suck you into a development path that you will come to regret following.