Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 13-04-2009
Member
 
Join Date: Apr 2009
Posts: 49
MySQL MyISAM vs InnoDB

hi,
When is it appropriate to use InnoDB table types?It is not easy just to say use "MyISAM or InnoDB";Would it make sense to mix and match MyISAM and InnoDB table types in my database based on the number of rows in the table?MyISAM memory and space usage, full text support, bulk insert capabilities and speed are known plus factors.
Reply With Quote
  #2  
Old 13-04-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: MySQL MyISAM vs InnoDB

hi,
First it is worth to note MySQL only saves very basic cardinality information for index prefixes for index stats and these rarely change. There is no histograms or any other skew metrics etc. MySQL optimizer also uses number of rows in the table for many decisions but this is computed live (maintained for MyISAM and estimated during query execution for Innodb). This basic information means it does not change whole that quickly at extent to affect optimizer plans.
Reply With Quote
  #3  
Old 13-04-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: MySQL MyISAM vs InnoDB

To see what engine your existing table are, run:
show table status;
Engine is listed second.

MyISAM has been the default storage engine since 3.23. To see what your server is setup to use for the default engine, run:
show variables like 'storage_engine';
Reply With Quote
  #4  
Old 13-04-2009
Member
 
Join Date: Feb 2008
Posts: 1,848
Re: MySQL MyISAM vs InnoDB

MyISAM in most cases will be faster than InnoDB for run of the mill sort of work. Selecting, updating and inserting are all very speedy under normal circumstances. It is the default engine chosen by the MySQL development team which speaks to its integrity, reliability, and performance.

InnoDB, or the OSX of the database-engine world, has emerged with some nifty features and created a niche for itself very quickly. Boasting features like row-level locking, transaction-safe queries, and relational table design are all very temping. The first two features really shine in a table that is constantly getting hammered like a logs, or search engine-type table. Since queries happen in the blink of an eye (faster actually) table-level locking(MyISAM) is sufficient in most other normal cases.

InnoDB recovers from a crash or other unexpected shutdown by replaying its logs. MyISAM must fully scan and repair or rebuild any indexes or possibly tables which had been updated but not fully flushed to disk.
Reply With Quote
  #5  
Old 13-04-2009
Member
 
Join Date: Oct 2005
Posts: 2,389
Re: MySQL MyISAM vs InnoDB

There are major advantages to INNO that far overshadow negatives you might come up with. First and most importantly is row level locking. You get this regardless of coding and it's the cat's ass. There is simple no way to run anything but a modest site on anything but. With table locking such as what MyISAM uses the entire table is locked for updates. INSERTs are ok but each time a record is updated it locks the entire table. It may not sound like there are many updates but each time a topic is viewed the view count gets updated. That's a lot of updates on a busy site. Also on a busy site those locks quickly spiral out of control causing meltdown. There is no deadlock, there are simply more requests for locks coming in than can be handled so the lock count becomes ever increasing, the site slows to a crawl and then crashes. This doesn't happen with INNODB. My site would last all of 3 minutes before becoming totally unresponsive without it.

The second major advantage of INNODB vs. MyISAM is reliability. INNO simply doesn't corrupt. I've been running my site for 4 years first on VB 1, then 2 then IPB 1.3 without ever having a database table or index corruption. It simply doesn't happen, at all.
Reply With Quote
  #6  
Old 13-04-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: MySQL MyISAM vs InnoDB

MyISAM tables are the default table type used by MySQL.

The main difference between MyISAM and InnoDB tables is that InnoDB tables support transactions and MyISAM tables don't. Unless you need transaction support in your database, you should always use MyISAM tables.

MyISAM creates slightly smaller tables and require less space than InnoDB tables, and you can optimise them for space/speed using the myisamchk tool, but InnoDB allows more features, especially foreign key contraints.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "MySQL MyISAM vs InnoDB"
Thread Thread Starter Forum Replies Last Post
cPanel - innoDB not enabled Parvati Technology & Internet 5 23-06-2010 10:35 AM
Mysql Error : Can't connect to local mysql server through socket ' var lib mysql mysql.sock' 2 roshan45 Software Development 3 07-11-2009 09:36 PM
enable innodb using phpmyadmin Tomthegreat Networking & Security 3 20-05-2009 01:19 PM
Restore corrupted MyISAM/InnoDB tables on MySQL 5.0 jean-paul martell Software Development 5 14-04-2009 09:03 AM
MyISAM Static Vs MyISAM Dynamic Sylvester.S Education Career and Job Discussions 2 24-10-2008 07:59 PM


All times are GMT +5.5. The time now is 09:32 AM.