This question could easily start a holy war. ;-) That said, I think the
following is generally agreed upon:
1) MySQL is one of the fastest databases around, particularly is you do
not need transactions.
does support transactions as you note, and has for years -- since 3.23.
Innodb is required, but you could certainly run debug on the SQLObject
table definition and then create the table yourself using innodb. I
don't know about SQLObject's specific support for MySQL transactions,
but MySQL supports many table handlers and both BDB and InnoDB support
transactions. (As does NDB with clustering.) Also, MySQL 5.0 Beta/5.1
supports the most important features, such as triggers, views, stored
procedures, etc. Most versions of MySQL currently included with
distributions can support things like subselects, foreign keys, etc.
Most of the more advanced features require Innodb. If you go this route
-- mix MyISAM for tables that don't need transactional support
(transactions, row-level locks, and foreign keys/cascade deletes will
slow down any database) and InnoDB for tables that do. MyISAM is very
fast, fast, fast. Another nice feature of MySQL is that it's basically
bulletproof and rarely requires any maintenance or administration. MySQL
is also used in very large installations (i.e., Yahoo! Finance is
completely run on MySQL, including real-time calculation of stock views,
craigslist.org, livejournal.com, etc.)
2) That said, MySQL doesn't support even close to the full SQL command
set, and PostgreSQL supports an incredibly large subset -- in many cases
surpassing even the 800 lb gorilla Oracle. PostgreSQL is a very powerful
and robust database. Another great thing about PostgreSQL is that there
are solutions available from multiple vendors for things like
load-balancing clustering and replication.
For new applications, I'm heavily biased toward MySQL for speed and
simplicity, but for rewrites/ports of existing applications from other
databases, PostgreSQL is always the best solution. I don't know enough
about Firebird or SAP-DB (now co-developed by SAP and MySQL) to be
These pages might be helpful:
http://dev.mysql.com/tech-resources/crash-me.php comparing various
databases; highlights weaknesses and strengths of MySQL.
From SQLite, I'd definitely consider MySQL first.. but do remember
this: it's not too painful to go from MySQL to Postgresql, but can be
much more painful to go the other direction, especially if you use some
of PostgreSQL's more advanced SQL features.
Alan Kennedy wrote:
> Greetings all,
> I'd like to solicit opinions on which database is the best backend to
> use for sqlobject.
> My primary requirements are, in order of importance
> 1. Robust transaction support
> 2. Robust concurrency support
> 3. Support for schema modification
> I've been using SQLite up to now, but I've found that the concurrency
> problems are a lot of hassle. Also, it's a hassle to modify the schema
> after creation.
> I read up on MySQL, but am concerned by the warnings in the
> documentation, i.e. "MySQLConnection supports all the features, though
> MySQL only supports transactions when using the InnoDB backend, and
> SQLObject currently does not have support for explicitly defining the
> backend when using createTable".
> I haven't used PostGres before, but it does look promising. General
> opinion seems to be that it is a very robust database. And I'm
> encouraged by the doc comment: "PostgresConnection supports
> transactions and all other features."
> Lastly, I have never even looked at FireBird. Is the comment in the
> documentation about Firebird still applicable, i.e. "Support is still
> young, so there may be some issues, especially with concurrent access,
> and especially using lazy selects".
> Thanks in advance for any info and opinions.
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> sqlobject-discuss mailing list