I've been watching the group for a long time, and there is a huge debate about MyISAM vs InnoDB for "large" tables.
It really does depend on what you want to use your table for, what hardware you running on, and what size you consider to be large! For me, I also have mainly numerical data, and 1-2 concurrent users at a time, generally only viewing the data, I found (to date, ongoing experiment) innodb with no performance configuration (same as default in supplied my.cnf files) tweaking slightly faster than a highly tweaked and studied MyISAM table with exactly the same data, server, application etc. Table sizes queried at that stage added up to 1GB. Under the InnoDB conversion, I ran into just under 2GB. MyISAM also has a slight maintenance (offline I run MyISAMChk frequently sorting the rows and indexes) overhead, which I understand InnoDB not to have. Many other sorts of bottlenecks creep in more easily at all layers of typical database usage, because ODBC, for instance, works (performance wise) differently on WIN XP SP1 and WIN 2K, fully SP-patched and updated, due to bugs under XP! JDBC works better or worse depending on which VM, and if you're following proper specification methods and good programming choices, or not! By wiser choice of ODBC settings under XP SP1, I had performance improve up to 8 times in my application. Under 2K, my performance merely doubled with the same settings. So OS, drivers, etc are huge players to be reckoned with as well. Bottom line, IMHO, benchmark them both, for your requirements, in your targetted environment(s). No respected DBA I know of makes any strategic choice without first benchmarking specific requirements, especially for "large" databases where users are going to be interactively querying them! User time, ultimately/normally, is the most expensive thing to waste. regards Rob -----Original Message----- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Wednesday 18 February 2004 14:48 To: Craig Robinson Cc: [EMAIL PROTECTED] Subject: Re: Which type of table to use? Craig Robinson wrote: >Hi, > >I intend to use MySQL for a database application, and I am not sure >whether to use MyISAM or InnoDB tables. There will be a very large >amount of (mainly numerical) data. The relations between the tables will >be reasonably simple. Which table type would you recommend? > The relationships between tables can be satisfied by either table type if they can be satisfied by the relational model. InnoDB does offer foreign key constraints though, allowing you to leave integrity checking to the database. InnoDB will also allow you to easily ROLLBACK transactions. MyISAM is faster in many cases, but InnoDB offers higher concurrency. > Also, I find >InnoDB tables take up about twice the space as MyISAM - is this correct? > > Yes, it is correct. One of the reasons is that InnoDB has to worry about keeping track of multiple versions of rows and which version of each row is to be seen by each currently running transaction. >Cheers, > >Craig Robinson. > > > > Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
