MyISAM vs InnoDB vs Something else

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

MyISAM vs InnoDB vs Something else

Post by GeXus »

I'm working on an ad server that needs to store impressions for all the ads, we're talking hundreds of millions of rows. Each row has 4 int columns and a date column. What storage method would you say is optimal for this? Not only does it have to just insert the records, but also do counts and lookups for reporting... there's also the option of storing on the file system... but I just don't know which would be best... any feedback would be great! Thanks!
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Re: MyISAM vs InnoDB vs Something else

Post by bdlang »

When all else fails, RTFM. ;)

Seriously, though, MyISAM should be fine; the only reason I'd typically choose InnoDB instead is if I require the use of transactions. Read the differences between them in the manual as posted and kick it around the office. Make sure you also read the section on optimization, especially the section marked 'optimizing database structure' and use the appropriate indexes.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Re: MyISAM vs InnoDB vs Something else

Post by GeXus »

Thanks, those links were actually very helpful!
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: MyISAM vs InnoDB vs Something else

Post by pickle »

InnoDB, I've heard, is superior when the number of reads vastly outweighs the number of writes, and you've got multiple millions of rows. It sounds like you're going to have more writes than reads though, so I'd stick with MyISAM.

MySQL also has a bunch more tools that work on MyISAM tables & don't on InnoDB. Just don't ask me to list them...
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply