 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / Databases / May 2004What does MySQL do better than PostgreSQL?
I've had some exposure to PostgreSQL and found it to be a pretty good DB, worked with Informix on HP boxes and MSQL on Windows. But comparing Open Source DBs, MySQL and PostgreSQL, which is better for small to medium sized workloads? > But comparing Open Source DBs, MySQL and PostgreSQL, which is better for > small to medium sized workloads? I would say MySQL but you'd have to define "workload" a bit better before anyone can say for sure. > I've had some exposure to PostgreSQL and found it to be a pretty good DB, > worked with Informix on HP boxes and MSQL on Windows. > > But comparing Open Source DBs, MySQL and PostgreSQL, which is better for > small to medium sized workloads? Without a demanding load, performance doesn't matter. I'd go with PostgreSQL because of the better support for the SQL language (subqueries, more normal implementation of SQL, fewer tricks to get used to), easier assurance of data integrity, and less restrictive licensing (you don't have to worry about being sued). If performance does become an issue, then it depends on what kind of load you've got. For lots of short connections, MySQL seems to be faster at the connection setup and teardown process, which makes it frequently used for web boards and the like. For more challenging queries over larger amounts of data (more along the lines of an enterprise application), the PostgreSQL optimizer does a better job. (I should qualify that I'm speaking from experience with MyISAM tables, which don't do transactions or many integrity constraints. If you want normal database functionality, I'm not familiar with MySQL performance with other table types. I suspect the MySQL advantage in establishing connections would dwindle if you've got a transaction taking place.) There are also specific usage patterns that may impact your usage quite a bit. For example, poor implementation of very large fields in the PostgreSQL JDBC driver might cause some problems (but admittedly, I don't know if MySQL is any better or not) if you intend to depend on that.  Signaturewww.designacourse.com The Easiest Way to Train Anyone... Anywhere. Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation > > I've had some exposure to PostgreSQL and found it to be a pretty good DB, > > worked with Informix on HP boxes and MSQL on Windows. [quoted text clipped - 26 lines] > don't know if MySQL is any better or not) if you intend to depend on > that. Chris, That was a very detailed and thoughtful response to an admittedly vague question, thank-you for covering the points that you felt highlighted the differences as you saw them! My experience with PostgreSQL was positive and what reading I'd done on MySQL made me wonder why it seems to be so popular with so many Web Hosts. Your explanation was perfect! Thanks, Dilton >That was a very detailed and thoughtful response to an admittedly vague >question, thank-you for covering the points that you felt highlighted the >differences as you saw them! My experience with PostgreSQL was positive and >what reading I'd done on MySQL made me wonder why it seems to be so popular >with so many Web Hosts. One the guys I work for much prefers PostGres, but I don't know why. I could ask him for you. SignatureCanadian Mind Products, Roedy Green. Coaching, problem solving, economical contract programming. See http://mindprod.com/jgloss/jgloss.html for The Java Glossary. > One the guys I work for much prefers PostGres, but I don't know why. I > could ask him for you. I'd appreciate that thanks Roedy! I'm thinking that similar to what Chris mentioned if one comes from the Enterprise world then PostgreSQL is more favored but MySQL works well for short duration requests which is what one has in the Web world. >I'd appreciate that thanks Roedy! I'm thinking that similar to what Chris >mentioned if one comes from the Enterprise world then PostgreSQL is more >favored but MySQL works well for short duration requests which is what one >has in the Web world. see http://mindprod.com/jgloss/postgresql.html SignatureCanadian Mind Products, Roedy Green. Coaching, problem solving, economical contract programming. See http://mindprod.com/jgloss/jgloss.html for The Java Glossary. > see http://mindprod.com/jgloss/postgresql.html Hi Roedy, That's a pretty good comparion, however: "PostGres has many data types, including autoincrementing primary keys" MySQL also has many data types, including autoincrementing primary keys "PostGres supports subselects" MySQL 4.1 now supports subselects > That's a pretty good comparion, however: > > "PostGres has many data types, including autoincrementing primary keys" > > MySQL also has many data types, including autoincrementing primary keys Aside from that, it's more normal in Postgres to not use serial columns and use sequences explicitly instead. When you plan to continue to manipulate the data you've inserted, that's an easy way to get the primary key back to your application, *especially* since the PostgreSQL JDBC drivers don't implement the getGeneratedKeys method. > "PostGres supports subselects" > > MySQL 4.1 now supports subselects It's worth pointing out, for anyone not already aware, that MySQL 4.1 is in its alpha release stage right now. It's hardly comparing apples to apples to say that MySQL has subselects, any more than to say that PostgreSQL has nested transactions; that is, the code exists for both, but neither has been endorsed as a released product. Signaturewww.designacourse.com The Easiest Way to Train Anyone... Anywhere. Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation > It's worth pointing out, for anyone not already aware, that MySQL 4.1 is > in its alpha release stage right now. It's hardly comparing apples to > apples to say that MySQL has subselects, any more than to say that > PostgreSQL has nested transactions; that is, the code exists for both, > but neither has been endorsed as a released product. Yes you're right of course. I forgot to mention that :-$ >That's a pretty good comparion, however: > [quoted text clipped - 5 lines] > >MySQL 4.1 now supports subselects Renney Thomas, the guy who provided this opinion did his detailed evaluation a few years ago when MySQL was considerably more behind. I checked out the subselects thing myself and the info I got was it was still only in beta. I gather than info too is now dated. Have updated the entry. SignatureCanadian Mind Products, Roedy Green. Coaching, problem solving, economical contract programming. See http://mindprod.com/jgloss/jgloss.html for The Java Glossary. > I checked out the subselects thing myself and the info I got was it > was still only in beta. I gather than info too is now dated. Yes it isn't a production release yet, sorry I forgot to mention that. > Have updated the entry. I hate to be picky :-P but "MySQL 4.1 alpha (preliminary version) now too has autoincrementing primary keys and subselects." MySQL has had autoincrementing keys since at least version 3.2, in fact I can't remember a time when it didn't and I've been using it for several years now. anyway, my experiences tell me mySQL is still not complete when you use features like subquery. Something like storeproc is also in very early stage. As such, why borther to stick with it if you have a better choice ? > > I checked out the subselects thing myself and the info I got was it > > was still only in beta. I gather than info too is now dated. [quoted text clipped - 11 lines] > can't remember a time when it didn't and I've been using it for several > years now. > anyway, my experiences tell me mySQL is still not complete when you use > features like subquery. Something like storeproc is also in very early > stage. As such, why borther to stick with it if you have a better choice ? Yes, MySQL's subquery support is incomplete at this time. They also still don't support views. SignatureLee Fesperman, FirstSQL, Inc. (http://www.firstsql.com) ============================================================== * The Ultimate DBMS is here! * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com) > That was a very detailed and thoughtful response to an admittedly vague > question, thank-you for covering the points that you felt highlighted the [quoted text clipped - 3 lines] > > Your explanation was perfect! My experience is that MySQL is far easier to install on Winblows, which is why I've been using it. PostgreSQL would otherwise be my choice, because I've had a long history with post-relational DBs. Luke > My experience is that MySQL is far easier to install on Winblows, which is > why I've been using it. PostgreSQL would otherwise be my choice, because > I've had a long history with post-relational DBs. Indeed, and prior to 7.5's native Windows port I'd never suggest using Postgres in production on Windows anyway. It will be interesting to see what 7.5 with its native Windows port does to the install process there. I understand you won't need to install Cygwin first, but I don't know much more than that. Signaturewww.designacourse.com The Easiest Way to Train Anyone... Anywhere. Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation > > My experience is that MySQL is far easier to install on Winblows, which is > > why I've been using it. PostgreSQL would otherwise be my choice, because [quoted text clipped - 5 lines] > I understand you won't need to install Cygwin first, but I don't know > much more than that. Thanks to Chris, Roedy, Murray, Luke, Joe and any others who may come along. Your feedback has been great! FYI I found a host that looks promising, they say that they support PostgreSQL if requested: http://www.oxxus.net/hosting_java.htm > My experience is that MySQL is far easier to install on Winblows, which is > why I've been using it. PostgreSQL would otherwise be my choice, because > I've had a long history with post-relational DBs. Hmm, you've had a long history with nothingness. Post-relational is just marketing hype. It simply has no meaning. Post-relational implies enhancing the relational model which those DBs don't. Try 'anti-relational'. SignatureLee Fesperman, FirstSQL, Inc. (http://www.firstsql.com) ============================================================== * The Ultimate DBMS is here! * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com) Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.