Slashdot Mirror
Comparing MySQL and PostgreSQL 2
Mr. Jax writes "6 years ago Mr Poet submitted the story Comparing MySQL and PostgreSQL. Since then both databases have evolved to wherever they are today.
Are the points raised 6 years ago still valid? What has changed? Are there other things to consider since then (e.g. licensing)?" This is certainly a valid question since both databases have had to evolve with the times. Have these applications been specialized to fit a particular niche market or are they both still strong competitors? What does the horizon look like for the development of these programs, especially considering the recent MySQL partnership with SCO?
Not only is this article just 2 links to some other slashdot articles, but the "comparison" of mysql and postgres article from 6 years ago.. Doesn't compare them! It's an article, like this one, asking for some comments.. So not only does this article add no news for nerds, it even misrepresents links to this very site, which the editors, again, are too lazy to even follow? Come on, people!
This sort of whoring-for-comments article should be a poll.
SCO employee? Check out the bounty
I'm using both, but mainly Postgres. From what I can tell:
... well, bad)
Postgres 7.5
Pros:
- Supports stored procedures
- Supports triggers
- Supports schemas
Cons:
- Heavy on resources
MySQL 4.0
Pros:
- Fast
- Easier to find PHP scripts that use it
Cons:
- Bad relational support (and yes, I know about InnoDB, but even then, it's a bit
- No stored procedures/triggers
- Easily corrupted by crashes.
I sense much beer in you. Beer leads to intoxication, intoxication leads to hangover. Hangover leads to sobering.
Not really. Oracle has the 'non benchmarking' clause that prevents you from doing that. If people were able to compare it to, say, PostgreSQL, and publish the results you'd see a lot pgsql boxes replacing expensive Oracle licenses. MS's SQL server is a total joke.
In recent versions of PostgreSQL, VACUUM still exists but is non-locking (i.e. it will not block concurrent database activity). There are tools available to run VACUUMs automatically as the database is in use, and recent versions of Postgres have been tuned so that the I/O performed by VACUUM should have less of an effect on the rest of the system.
http://www.geocities.com.nyud.net:8090/mailsoftwar e42/db/
It was updated March '05.
I don't have any thoughts about the more general question, but PostgreSQL is much better at storing spatial data than MySQL. MySQL has spatial functions built in, but it only supports a subset of the OpenGIS functions (basically anything that can be done entirely with bounding boxes). PostgreSQL uses an external modulem PostGIS, which supports the full OpenGIS specification and a bunch of other extension functions besides. I've used MySQL by default simply because it is more familiar to me, but I've switched to PostgreSQL for my current project simply because of the spatial data module.
Friends don't let friends misuse the subjunctive.
MySQL's biggest problem is that if you try to update or insert with invalid data, in many cases it successfully inserts wrong data. PostgreSQL doesn't do that.
PostgreSQL has this nice Object-Relational model where tables can be derived from each other, but there are some nasty bugs that mean I think those features are still best avoided.
Overall, though, I think PostgreSQL is by far the better RDBMS.
because I started buidling a proprietary project for my company and without really looking into the MySQL licensing decided to go with MySQL (I know I am stupid). Anyways, I also purchased SQLyog (A GUI for working with MySQL) to speed up development.
Now I am stuck. I have to either a) switch to postgres, which does not seem too bad an option, or b) pay for MySQL (and prices are not even listed online *shudder*). What is the learning curve for postgres? Are there any good books? What about tools like SQLyog?
GPL - Got punk'd lately?
Except for big iron (where DB2 dominates) and Micorosft environments (where SQL server dominates), Oracle is the dominant player.
I recently moved my deployments from Oracle to MySQL because:
1. MySQL supports all of the Oracle features you need to build and operate an enterprise software system.
2. MySQL's new administration tools are significantly better than Oracle's out of the box tools (This is why a year ago I refused to use MySQL for production, and now I've switched everything).
3. MySQL is much easier to manage. I don't know anybody who runs a heavily loaded Oracle server in production without spending significant $$$ on DBAs and commercial tools. I feel quite comfortable doing this with MySQL.
4. MySQL performs pretty much the same as Oracle out of the box (and I think it is easier to tune).
5. MySQL's supposed gotchas pale in to comparison to Oracle's. When I first used MySQL BLOBs it simply worked. I opened up the administration programs and I could actually see the images in the database. It was so beautiful I wanted to cry. I can't count the number of times I went through Oracle BLOB/CLOB hell with different platforms. (Not just getting them in there, but actually getting them to work with third party applications which is the real pain.)
I think that anybody deploying Oracle for non-Oracle applications is going to have to very seriously consider MySQL if for no other reason than all the DBA salaries you can get rid of.
If you want to buld a $1M cluser, stick with Oracle (for now). If you want to run application specifically designed by (or for) Oracle, stick with Oracle. Otherwise, switch at the first opportunity.
Our company has developed an app used at several hundred sites on the Interbase/Firebird platform. (Firebird is now the only open source version). It is stable, quick, low maintenance with support for transactions, triggers, row level lockinge, etc. I would consider MySQL for web development because it comes preinstalled on many hosts and because of the number of tools available.
This page is the best document I've seen comparing each of the majordatabases (Oracle, Postgresql, DB2, MySQL, SQL Server) not directly against each other, but against the SQL Standard. In cases where at least one of the databases differs from the standard, this guy's article shows both the SQL called for by the standard, and how each of the implementations may either follow or deviate from the standard.
mysql> select count(*) from users where id not in (1, 2, 3, 4, 5);
+----------+
| count(*) |
+----------+
| 93 |
+----------+
1 row in set (0.12 sec)
mysql> create table strings (stuff varchar(8));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into strings values ('abcdefghijk');
ERROR 1406 (22001): Data too long for column 'stuff' at row 1
mysql>
I'm using PostgreSQL and MySQL, from my experience:
1. I've never encountered corrupted data with mysql (It seems to be urban legend), and I have worked on tables with billions rows for two years.
2. PostgreSQL has more features and/or is more complete (simple example can be auto_increment vs. sequences)
3. PostgreSQL is heavier, and I hate statistics collector subprocess via udp (which seems to be eating 1-2% cpu all the time)*
4. mysql isn't much (if any) faster.
* - it's unlikely but possible my configs are to blame.
I'm certainly no power user but Postgres strikes me as an extremely well featured DB. I use MSDE / MS SQL server at work for an app with transactions, stored procedures, triggers and views and expect it would be straightforward (not trivial but straightforward) to port it to Postgres.
Sadly though, there's no incentive since MSDE is "free". Basically MSDE is a cut down SQL server which is no bad thing. But I'd love to see Postgres bundled in a similar form on XP - just the engine but nothing else. I reckon lots of apps need a database engine and Postgres fits the bill quite nicely. It would even be a smaller redistributable than MSDE too.
So, if I try to insert, say, a string of 10 chars into a varchar(9) field, what will it do? Will the magic version 5 reject it, as ever real database does, or will it truncate it silently, just as Toy databases (ala MySql 4.x) are wont to do?
What about the whole not-null thing? You know, if a field is set to NOT NULL and you don't populate it when you insert a row, a real database will reject it, where as a Toy database will accept it (MySql 4.x again!) and populate it with ... some other value.
Yeah, right.
Get your facts straight coward:
_ 948.html
"As part of the agreement, the companies will work together on a range of joint marketing, sales, training, business development and support programs"
http://www.mysql.com/news-and-events/news/article
burnin
mysql> select count(*) from users where id not in (select id from users where id%2 != 0);
+----------+
| count(*) |
+----------+
| 48 |
+----------+
1 row in set (0.01 sec)
mysql>
Speak for yourself, I'm an IT Manager for a Communications company and we've found MySQL to perform far worse for heavy load usage as compared to Postgres. We've migrated completely to Postgres with no regrets and significant performance increase.
If you're hearing rhetoric about Linux, open source, or Mac and everyone's bashing Microsoft, you've found Slashdot.
To each his own, of course, and the situation may indeed have improved. I don't see any clear advantage that it has over PostgreSQL, though, and I doubt it'll ever gain much momentum.
Dewey, what part of this looks like authorities should be involved?
One thing to consider - collations and Unicode support. Believe it or not, folks, Postgres does NOT support case-insensitive string comparisons. Or, more exactly it does, but you end up doing full table scan and converting everything into upper/lowercase, which is not an option on all but the smallest of the datasets. And even converting to upper/lowercase is a BIG problem for PostgreSQL, because it's UNICODE support is quite poor. So if your project has even remote possibility of using non-English textual data in lookups, steer clear of PostgreSQL.
There's a discussion about including support for IBM ICU, but as of right now there's no proper collations/unicode support in PgSQL, aside from storing character data in UTF-8.
MySQL is much better in this regard.
http://www.huihoo.com/postgresql/mysql-vs-pgsql.ht ml
Changes/corrections since that study was made:
PostgreSQL now natively supports BLOBs directly in tables (bytea type) as opposed to using oid references.
PostgreSQL has always had "better than row level" locking, Multi-Version Concurrency Control.
PostgreSQL has added Java and Ruby to its list of stored procedure languages.
----------------
Now, here's the caveat. MySQL 5.0 is still marked as a "development release (use this for previewing and testing new features)" so I didn't include it in the above. If we include MySQL 5.0, we must also include PostgreSQL 8.1, currently in beta.
MySQL 5.0 adds views, stored procedures, triggers, cursors, the bit data type, up to 65K varchar fields, two new storage engines (federated and archive), and a strict mode.
PostgreSQL 8.1 adds two-phase commits, a role system, shared row level locks using SELECT, and many speed improvements.
The strict mode in MySQL is most exciting to me. I always bought the argument that MySQL could have fewer features in exchange for greater speed. But there is no excuse (in my opinon of course) to accept random strings into numeric fields and other such contrivances (MySQL gotchas). Data integrity in a database should not be an optional feature.
- I don't need to go outside, my CRT tan'll do me just fine.
Do you know MySQL has had transactions for years now? And SAVEPOINT transactions (like Postgres, I believe). And views in 5.0. The table-locking engine you're thinking of (MyISAM) hasn't been current for maybe 4 years. If you can't handle 100 users at once blame your design, not MySQL -- MySQL powers Slashdot :)
Please, Oracle has a ton of features that just aren't there in PostgreSQL. For features a mom&pop shop need, Postgres is usually fine. But for serious enterprise work PostgreSQL is not anywhere on the same playing field as Oracle.
I'm not dissing PostgreSQL. I like it, and I use it. I also use MS SQL server and when I have no other choice, MySQL.
The feature list difference between each of the databases is quite large. Simply saying "PostreSQL compares very well to Oracle" is NOT informative.
I once asked on slashdot about why people use MySQL and how does it compares to PostGreSQL. Got a bunch of interesting responses.
Prescriptive grammar:linguistics
There isn't one from MySQL AB.
Actually there is a press release on MySQL's website:
SCO Partners With MySQL AB to Lower Costs and Increase the Power & Scalability of Modern Database Solutions
FalconShould there be a Law?
There are good reasons to pay someone for support, if the people you're paying know their stuff. If you're building enterprise level, mission critical data warehouses, you'll want immediate access to expert help when things go horribly wrong. And Sorbannes/Oxley reinforces that need.
For those seeking paid support, there are several companies working to do interesting things with Pg:
There are some other outfits dedicated to Pg support, but I can't recall the particulars...
Meanwhile, MySQL still seems to be having difficulty getting stored procs and real views released...5.0 is starting to make Longhorn's development schedule look like a quarterly maintenance release.
It's also interesting that TFA didn't mention the rise of alternatives ranging from SQLite (which pretty much does everything that folks used MySQL for in the first place, but wo/ any license confusion), to Firebird, to the recently open'd Ingres.
007: "Who are you?"
Pussy: "My name is Pussy Galore."
007: "I must be dreaming..."
Debian: apt-get install postgresql (or use Synaptic)
;-)
Gentoo: emerge postgresql
Fedora: rpm -Uvh postgresql-8.0.3.i386.rpm (or select the "Database" package during install)
Windows: setup.exe
Easy administration from Windows, OS X, Linux, and BSD with PgAdmin.
Or were you talking about a manual install? Sure that's harder, but most of us don't do manual installs. Just those crazy Slackware folks and their ilk.
- I don't need to go outside, my CRT tan'll do me just fine.
but ALL dbms's are slightly different from eachother
MySQL is far away and completely alone in it's flavor of SQL and behavior. There are all kinds of strange things it does, like use the "`" to quote identifiers. Oracle, PostgreSQL, SQL Server, &c. all have their differences. But they all look a lot more like eachother than any of them look like MySQL.
The biggest and most 'incompatible' difference between postgres and mysql is the autoincrement field really, the rest is just small fish to fix.
Actually, the biggest differences are regarding types and constraints, in my opinion. For instance, MySQL thinks February 31st is a date, PostgreSQL does not. PostgreSQL enforces constraints, MySQL does not. MySQL NULL handling is non-standard.
Social scientists are inspired by theories; scientists are humbled by facts.
I disagree.
Putting everything (by everything I mean business logic) in the DB is the only sane way to keep your data consistent across multiple access methods. You simply can't thow data at a DB and then try to code and maintain consistent business logic in a half dozen client apps. You might be able to get away with a shared client access lib but even that can get messy.
Let's face it: Your data's in the DB. Why pull it all into the application to work on some small subset? Do all the queries and joins and clauses and increments in the database. The DB knows best where the data is and how you're going to be tinkering with it (so long as you give it sufficent hints), so it's the only sane method to access your data in a logical fashion. That's precisely why all these scripting languages and language interfaces exist.
I too use XML-RPC and SOAP (moreso the latter it seems, as XML-RPC is a little too light IMO) to access my data, but you can bet your sweet bippy I'm having the DB do as much as possible in order to transfer as little data as possible across my app-db link.
Most databases are descended from the Interactive Graphics Retrieval System INGRES. The original coder of Ingres later started a follow on project called Postgres and when SQL was standardised it became PostgreSQL. Basically, there are only about 4 database families: SAP, Ingres, DB2 and Oracle. I prefer using Postgres simply because it is funded by the nice US taxpayers through DARPA. If it is good enough for the military, it should be good enough for me...
Oh well, what the hell...
I've read a few of the replies to this story. It's interesting to read some of the pro-PostgreSQL peoples' opinions. They're rather dated.
The more I learn about MySQL (from the perspective of someone who was initially gung-ho about PostgreSQL), the more I realised the shortcomings of MySQL weren't really shortcomings. They were misunderstandings. Yes, this can sometimes be as bad, when a default option is a stupid option (like table-level locking, as the parent and other PostgreSQL fans complain about).
Then I quit that job and went to work at Friendster, which is also a big MySQL shop. What I learned then was that when used properly, MySQL can scale to amazing proportions. Millions of transactions per hour (I won't be too specific being as I don't want to be sued into oblivion now that I'm an ex-Friendster employee).
Keep in mind that Friendster isn't alone. Google and Yahoo! use MySQL. For production loads. Big, big production loads.
What I didn't like about PostgreSQL was the weird licensing problems. Yes, bizarre as it may be, the BSD license they chose over GPL causes it to be bizarre. You can't get replication without downloading some weird third-party patch and recompiling (because the patch is GPL). Screw that. MySQL has it built in to the supported binaries you get from their site.
Without replication, your DBMS is useless. It's pretty clear from reading the parent post that Michalf doesn't really understand replication. If he did, he might think a moment about his statement that MySQ can't scale to more than 100 users at once. Friendster had millions (at once). Yahoo! has at last estimate nearly a hundred million users at once.
Last I checked PostgreSQL (admittedly, 6-9 months ago?) it just wasn't viable. Really replication was about the only thing holding it up, except I know another engineer who worked extensively with PostgreSQL internals (hacking it up to create a DBMS cluster, actually) and he said their I/O internals were bad/slow. Hopefully he's wrong, but I know before I deploy PostgreSQL I'm going to be carefully benchmarking it before doing so. Keeping in mind that I never deploy an RDBMS in a tiny little "more than 100 users" environment like the parent poster.
Sorry for the long-winded rant. It's just that I've been wishing/hoping/praying PostgreSQL would be the winning RDBMS in this battle for years, and every time I think it's going to be any good, it goes and shoots itself in the foot somehow, which makes me sad. Currently, I'm still a fulltime MySQL DBA.
Caveat: Much of what I've said here only applies in high volume RDBMS environments. If your environment is low volume, PostgreSQL may be a better choice.
fifth sigma, inc.
I loved the CHAR type section, specifically the MySQL entry:Beautiful.
- I don't need to go outside, my CRT tan'll do me just fine.
The one good thing I have to say about mysql is that its multi-user friendly for hundreds of accounts.
Not really. PostgreSQL had MySQL beat there. You can assign permissions to groups. With large numbers of accounts, this becomes problematic with MySQL.
PostgreSQL 8.1 beta further improves this by supporting the concept of roles. A role is like a user or group except that they can be nested. This allows even easier administration of databases with hundreds or thousands of user accounts.
Users on the web dont need something heavy unless they are a commercial website. Also there are a ton of php and perl scripts and tools for users to use.
Commercial as in what? A mom-and-pop web-store? I would call that commercial and because MySQL will *truncate* numbers to fit fields if they are too big, I would say PostgreSQL is needed for that.
You are right in that MySQL is quite adequate for content management provided (of course) that you don't need to integrate it with other buisness apps. If you do, you can either go to PostgreSQL (and take advantage of schemas) or use PostgreSQL with DBI-Link to pull the data from MySQL. DBI-Link is easily one of the coolest new projects for PostgreSQL as it allows any data source reachable via Perl's DBI interface to be seen as a table from within PostgreSQL.
LedgerSMB: Open source Accounting/ERP
No benchmarks here but benchmarks are largely useless in the database world anyway unless they are run on your specific application.
Oracle: Very portable database, replaces many OS functions and is extremely tunable. Downside: $$ and the fact that the tuning options are extremely complex allowing your DBA to spend all his time tuning the database, and your second DBA to spend all his time tuning the tables..... (/sarcasm)
One of the odd problems with Oracle is that empty strings and nulls are seen as equivalent (and Oracle DBA's seem to think that an empty string and a null are the same thing). The general concensus in the RDBMS industry is that these are not the same.
MS SQL Server: A Windows-only RDBMS which is tightly integrated with Windows in terms of memory management. Quite extensible, less costly and simpler to administrate than Oracle. Will tie you to Windows. Troubled security history.
PostgreSQL: An Open Source RDBMS designed to target Oracle's market. Extremely powerful and full featured. Attempts to tune itself to the greatest extent possible and relies on the OS for additional tuning. Downside is that it is not as widely used as the others listed above. Stored procedures are available in a much wider number of languages than in any other RDBMS in this comparison.
MySQL: A popular open source database manager (neither really relational nor a management system). Provides a simple non-standard subset of SQL for the interaction with various resources. Downside is that it does not do much integrity checking and does not enforce much integrity (valid dates include 0000-00-00 and 2004-02-31). Furthermore it will *truncate* numbers that are too large to fit in a number field making it unfit for any purpose where it must track money. It is more widely used than any other open source RDBMS.
FirebirdSQL. A good RDBMS designed really for Windows but ported over to UNIX/Linux. Fairly extensible and stable but largely undocumented. Lacks many of the data types available in all other databases listed here.
LedgerSMB: Open source Accounting/ERP
What does the ANSI standard say this means?
SELECT ('Hello ' || 'World!') as greeting FROM my_table;
What does it mean in MySQL?
Yes, every RDBMS has extensions to the standard and this is important but MySQL *breaks* standard behavior in many profound ways.
Another example: What does the ANSI standard say should happen here:
CREATE TABLE my_table (
myfield CHAR(4)
);
INSERT INTO my_table (myfield) values ("Hello World!");
What does MySQL do?
MySQL does the same with numbers.
These are areas where standard behavior is actually broken by the RDBMS.
LedgerSMB: Open source Accounting/ERP
You could also use the server-side cursor and scroll forward without sending that info to the client.
LedgerSMB: Open source Accounting/ERP
I guess that by "nested loops" you really mean deep joins, e.g.
....
select books.title, publishers.address, cities.timezone
from books
join publishers using(publisher_id)
join cities on(address_city=city.name);
PostgreSQL - and presumably most of the other DBs - will guess how many rows will match based on a precomputed statistical analysis of the tables and choose one of several different join algorithms, as appropriate. One of the possible algorithms is nested loops, i.e.
for b in books
for p in publishers
for c in cities
if b.publisher_id = p.publisher_id
and p.address_city = c.name
This is obviously very inefficient in the cases where the data sets are large; you'll get much better results using other techniques using e.g. hashing, or a merge-sort type approach. What indexes are present is also important.
PostgreSQL has the "EXPLAIN ANALYSE" command that you can use to find out what strategy it has chosen for your query. Just about the most common FAQ on the PostgreSQL mailing lists is "Why is PostgreSQL using an inefficient method and ignoring my indexes?". The answer is nearly always "its statistics have got out of date, run a periodic cron to update them" or "it is confused about the relative cost of reading from disk and sorting in memory, tweak something". Less often the answer is "add another index" or "re-write the query".
By poking around a bit you can nearly always get it to do the right thing. In your case, if PostgreSQL really is taking 18 hours vs. 5 minutes (200x slower), then do go and ask on the lists. Someone is sure to help if you can show an "EXPLAIN ANALYSE" output. It could be that MS_SQL is faster, but I find it inconcievable that the difference is that great.
> Postgres does NOT support case-insensitive string
> comparisons. Or, more exactly it does, but you
> end up doing full table scan and converting
> everything into upper/lowercase
The case-insensitive comparison is done like this:
select * from T1, T2 where lower(x)=lower(y);
If you have normal indexes on x and y, this won't use them and you'll get a sequential table scan. To be useful for this query, you need indexes on lower(x) and lower(y), e.g.
CREATE INDEX idx1 ON T1 (lower(x));
CREATE INDEX idx2 ON T2 (lower(y));
This is described on the reference page for CREATE INDEX.
I suggest that you ask on the mailing list next time you find a "showstopper" problem - people really are using PostgreSQL for serious applications, and an obvious thing like this would have been fixed long ago.
That's the primary reason, as far as I can see.
If you're not part of the solution, you're part of the precipitate.
If at first you don't succeed, skydiving is not for you
Breakfast served all day!
Can you be more specific? How often does slashdot go down?
Slashdot has a subtle ``down'' state where they only serve static pages. It causes neat things to break like the RSS feed that I get for my home page (any request returns a static page).
Wikimedia Foundation also runs on a small cluster of MySQL servers
Perhaps you don't remember their recent outtage that took the entire thing off the internet for a day or two while they had to completely rebuild their database from backups. All of the mySQL apologists were quick to point out that databases should be expected to be all corrupt and stuff when they lose power. Users of real databases were amazed that anyone would think that.
-- The world is watching America, and America is watching TV.
No other RDBMS has had the number or severity of viruses target it as MS SQL Server. Need I remind people of the SQL-Slammer worm? And this was not about blank SA passwords (unlike previous worms).
SQL-Server holds the dubious honor of being the host for the virus which spread around the world fastest.
LedgerSMB: Open source Accounting/ERP
I'm not sure why I don't see this mentioned yet, but this wikipedia article has a great grid that compares major database systems.
> as of right now there's no proper collations/unicode support in PgSQL, aside from storing character data in UTF-8.
Yeah, dipshit: the solution is store your fucking character data in UTF-8.
The money you are spending for Oracle in the above case gets you some features that SQL Server flat out doesn't have. Like all things, you do have to know what you are doing with Oracle, but, if you do, you get some features that make or break a big, big application.
uber partioned indexes
uber materialized views
outstanding mvcc transaction support
SQL Server 2005 is supposed to have some of these features in it, plus the kitchen sink, but then I would expect the price to move on that.
The other thing is that with Microsoft you generally are going to spend an extra $2000 / developer for tools.
This is my sig.
We haven't had any serious MySQL load problems in over a year, with the exception of one targeted DDoS which wedged up our search DB slave for a while. Slashdot hasn't had any MySQL reliability problems since we moved to 4.0. Our master DB has been running the same version of 4.0.x since early 2003 and it just keeps going, it never crashes. Later versions of 4.0.x are probably more reliable, but we have no need to upgrade because it just works. The only time it went down was last month when the OS finally threw a kernel panic, which sucked, but wasn't MySQL's fault.
Anyway, the point someone was trying to make is that MySQL isn't ready for high-traffic enterprise sites, which I hope we can all agree is just silly. Slashdot's not even the best example, go look at Wikipedia, CraigsList, LiveJournal, Yahoo, Google, etc.
1: ANSI is "Hello World" MySQL is '0'
2: ANSI is error, and abort the inserting transaction. MySQL inserts 'Hell'.
Another case in point:
mysql> create table test (
-> test numeric(4,2));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test (test) values (10000000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+--------+
| test |
+--------+
| 999.99 |
+--------+
1 row in set (0.00 sec)
So if this number was important and meant something (which it would in production) you just entered bad data into your database!
LedgerSMB: Open source Accounting/ERP
Then arrange to have the benchmarking done in a country which won't uphold anti-competitive bullshit clauses (and when Oracle protest that the license lets them sue the guy in the jurisdiction of Buttfuck, Illinois, will tell them where they can stick their extradition request).
When you're sueing someone, there's no extradition--that's solely for criminal proceedings. There is no analogous concept in civil litigation. It doesn't matter WHERE the violation of the contract takes place. You could have someone in Venezuala, or on Mars, perform the benchmarking, and you'd STILL get sued in a California court (assuming that's what Oracle wrote into the license agreement).
So if Oracle has a contract/license agreement with a customer that says "no benchmarking", and another clause that says "all disputes will be settled in Marin Co., CA", they don't have to bother with Buttfuck, IL at all:
1) Oracle files lawsuit against customer in Marin Co. court.
2) Marin Co. court looks at the contract clause governing jurisdiction, agrees that Marin is a valid court to hear the case.
3) Lawsuit proceeds.
If the defendant doesn't respond or show up, Oracle automatically wins the suit by default, and a judgement is entered against the defendant. Then Oracle has a court order, valid in EVERY other county in the USA, demanding that the defendant pay the judgement.
And Buttfuck, IL will enforce the order.
(If it were THAT easy to get out of a contract clause, wouldn't
The nested loop is a type of query plan not a type of SQL Statement. It is hence a feature of an implimentation and not of the language.
For example.
Suppose I do:
SELECT * from mytable_1 where f_key IN (select p_key from mytable_2 where condition IS TRUE);
The simplest way to try to do this is to select every p_key from mytable_2 where condition IS TRUE and then query table_1 for the row in which mytable_1.f_key = mytable_2.p_key
This is the nested loop. It wasn't too long ago when nested loops where the default query plan for all types of joins. Now (for obvious reasons) they are the plan of last resort. More likely, PostgreSQL would choose a merge join for this type of subquery, or so we would hope.
Subqueries used to be popular when all joins were handled via nested loops because you could force the RDBMS engine to run fewer loops by forcing the inner-most subquery to be the one returning the fewest rows. Nowadays, all really good RDBMS's rewrite subqueries as if they were joins and will try to do other query plans on them instead.*
Does this make sense?
My favorite nested loop nightmare was when PostgreSQL was running a nested loop against an empty table thousands of times and eating up CPU activity doing it. Three seconds or so of doing something entirely meaningless.....
* This is not always possible. I have written many queries looking for possible duplicate entries into a database. These often run nested loop query plans because of the complexity of the search conditions in the self join.
LedgerSMB: Open source Accounting/ERP
Care to name a couple? DB2 comes from System/R. Oracle seems to be home grown. Sybase came indirectly from Britton Lee, but was mostly written from scratch. SQL Server came from Sybase. Certainly RTI's product was based on Ingres.
The original coder of Ingres later started a follow on project called Postgres and when SQL was standardised it became PostgreSQL.
Hate to break it to you, but there was a HELL of a lot more than one person coding Ingres, over a very long time.
Joe Kalash
Chief Programmer, Ingres Project
1982-1985
(I may still have some business cards to prove it)
Well, I hate MySQL as much as the next poor slob who had an old version eat a lot of his data... but you're dead wrong on the Wikipedia point.
Wikipedia has never had a substantial mysql related failure. The outage you're talking about was due to the upgrade to mediawiki 1.5, it was a planed and announced outage that took about as long as we announced it would take, although we'd been hoping for about half the time. MySQL performed flawlessly during this process which involved churning around more than 80gb of data and hundreds of millions of rows.
The only MySQL related issue that was had in the whole thing was a MySQL quirk triggered bug in mediawiki 1.5 that wasn't discovered until we went live with the new code. A race condition due to table level locking was causing hard cpu pegging and timeouts as every session tried to process the expired blocked user list at once. This wasn't directly a MySQL bug but wouldn't have been an issue with PgSQL (due to MVCC) or if our software wasn't coded with a very MySQL mindset (treat the database like a dumb object store) with 99.99% of the smart stuff in the same PHP that serves every page up.
MySQL MaxDB (formerly known as SAPDB) is comparable with Postgresql in terms of features. It suffers a similar lack of recognition with hosting companies and php developers. Ingres is another "enterprise" ready db that is available open source.
Who supports XA Transactions?
MySQL - Not yet (planned in 5.0.12?)
SAPDB/MAXDB - Yes (limitations?)
Postgresql - No - in the works (8.1/8.2?)
Firebird - Yes
Berkeley DB - Yes
Ingres - Yes (limitations?)
Pricing vs Proprietary
Oracle: $58K CDN per CPU + 10% maint
DB2: $55K CDN per CPU + 10% for maint
MS SQL Server: $3K CDN per year per server (enterprise edition)
MySQL MAX DB: $1,800 CDN per CPU + 10% maint
Postgresql: Free + Support
Ingres: Free + support
Wikipedia has never had a substantial mysql related failure. The outage you're talking about was due to the upgrade to mediawiki 1.5, it was a planed and announced outage that took about as long as we announced it would take, although we'd been hoping for about half the time.
No, the outtage I'm talking about was described on the wikipedia site as having been caused by a power outtage that caused database corruption.
Here's the slashdot article to jog your memory.
-- The world is watching America, and America is watching TV.
http://ask.slashdot.org/users.pl?op=edithome
You have about 20 options for date/time display at the top.
You got me. Nothing serious, anyway.
With updateable views, you can separate the physical data storage mechanism from the way it is presented to the application.
mmm... but updatable views are problematic on a number of levels. For one thing, they don't work reliably, since you can't update a derived value, for instance. And theroretically they're a mess, since you should be updating a transient derived relation that strictly speaking ceases to exist before you even issue the update.
I'm not sure I'd want users inputting data into a data warehouse via the same view that they use to access the data anyway. There's too much derived and summarised data kicking around for my liking.
It does sound a cool way to present a read-only interface though.
Don't let THEM immanentize the Eschaton!