Difference between revisions of "MySQL"
m |
(→Scott Cain's thoughts on why you shouldn't use MySQL for Chado) |
||
| Line 8: | Line 8: | ||
Note: I plan on fleshing this out more, but I wanted to get my thoughts down while I'm thinking about it. | Note: I plan on fleshing this out more, but I wanted to get my thoughts down while I'm thinking about it. | ||
| + | |||
| + | MySQL is a very good database for some things: it is very fast as a read only database, and so is quite good for driving data displays like [GBrowse]. My experience outlined below describes why I learned to distrust it for 'important' data. | ||
*The ability to either have or not have transactional integrity (and look out if you accidentally forget to declare one of your tables as INNODB). See http://sql-info.de/en/mysql/transaction-innodb-table.html, http://sql-info.de/en/mysql/database-definition.html#2_3 and http://sql-info.de/en/mysql/database-definition.html#2_4 | *The ability to either have or not have transactional integrity (and look out if you accidentally forget to declare one of your tables as INNODB). See http://sql-info.de/en/mysql/transaction-innodb-table.html, http://sql-info.de/en/mysql/database-definition.html#2_3 and http://sql-info.de/en/mysql/database-definition.html#2_4 | ||
Revision as of 13:13, 31 January 2008
MySQL is a popular open source database management system (DBMS). It's generally considered to be the easiest of all relational database management systems to install (Unix or Windows). It's not always viewed as favorably by database experts as it lacks certain features that they may consider useful, but one cannot deny that MySQL has proved its utility in both the open source and commercial worlds. An excellent first database for those who have no prior experience with relational databases.
See also:
- Databases and GMOD
- PostgreSQL - The DBMS used with Chado.
Scott Cain's thoughts on why you shouldn't use MySQL for Chado
Note: I plan on fleshing this out more, but I wanted to get my thoughts down while I'm thinking about it.
MySQL is a very good database for some things: it is very fast as a read only database, and so is quite good for driving data displays like [GBrowse]. My experience outlined below describes why I learned to distrust it for 'important' data.
- The ability to either have or not have transactional integrity (and look out if you accidentally forget to declare one of your tables as INNODB). See http://sql-info.de/en/mysql/transaction-innodb-table.html, http://sql-info.de/en/mysql/database-definition.html#2_3 and http://sql-info.de/en/mysql/database-definition.html#2_4
- Silent dropping or truncation of data (To me, this is a VERY BIG DEAL if you care about your data.) See http://sql-info.de/mysql/gotchas.html#1_13 for a simple example that is still broken on MySQL 5.0.45.
- See the story below about silently throwing away data when inserting it; while MySQL 5 doesn't appear to be affected by this, you might see why my perception of MySQL is perhaps a little shaded by these events.
- Poor support for triggers (for example, you can't have more than one trigger that has the same trigger action time and event and triggers to not get executed when the event is caused by a cascading foreign key action (which Chado makes considerable use of)).
- We already have several years of development of tools devoted to PostgreSQL. I don't have the time to devote to porting, especially since PostgreSQL is an excellent RDBMS.
