Re: MySQL - PostgreSQL - DB2 - What?
I have worked with Oracle, MS-SQL, Sybase, PostgreSQL and MySQL. For smaller applications like
you are describing, MySQL wins hands down.
I currently have one table that has over 5 million rows (distance between any two zip (postal)
codes that are >= 50 miles apart in the US), and it works in real time with no problem. Most
complex query in the app is done with a union of four queries, each reading from four tables
(one of which is the zip codes), with probably a half dozen "where" clauses each and, as I
said, the response is in real time. That is on a 1.7Mhz machine, 512M DRAM, 10,000rpm SCSI in
a hardware RAID 5, though in tests it worked just fine on a 500Mhz machine with 128M and a
single SCSI HDD.
Only time I've been frustrated with it was when I created the zip code distance table by doing
a cross join of a zipcode/lat/long table with itself, then using floating point math to calc
the distance. That took about 2.5 hours on a 500Mhz computer with 128M RAM, which is
reasonable as far as I'm concerned.
PostgreSQL is fast also, though I haven't tested it with that load. I would assume it would
comparable. However, I hate managing PostgreSQL. Don't know why, but I always seem to have
problems when there is an upgrade and it drives me up a tree. (MySQL has very non-standard
permissions, by the way).
I would say that for under 10M rows in any table, they are both good. That may be
conservative. There is also the fact that MySQL has been updated quite a bit, and getting the
newer (4.???) version is well worth it. I chose PostgreSQL at one time because I love
subselects, but now MySQL does them also, plus transactions and (if you are of an entirely
different mindset from me) will support foreign keys Real Soon Now. And unions. Finally,
Basically, if you want full SQL-92 right now, I think you still have to go PostgreSQL. But, if
you don't need the whole thing, administration of MySQL makes it the winner for me.
> We're planning a new website where we will use a DB with 500.000 to 1.000.000
> records. We are now deciding which database server we will use. We've read
> that MySQL has big problems from 150.000 records and more. Also we've read
> that PostgreSQL is very slow on such records.
> But we don't have any experience, so we must rely on other people experience.
> I'm sure there are some stories about DB servers, like MySQL being the fastest
> ever, or MySQL functionality being the most ridiculous ever (can't do certain
> subselects, triggers...).
> What do you think of that stories? Which DB server would you use?
> To UNSUBSCRIBE, email to firstname.lastname@example.org
> with a subject of "unsubscribe". Trouble? Contact email@example.com
Latest survey shows that 3 out of 4 people make up 75% of the world's population.