Page 1 of 1

database type....

Posted: Mon Jan 07, 2008 10:11 pm
by arpowers
Hey everyone.!

if I am working on putting together a highly transactional site, similar to a facebook..;

what is the correct type of database I should use? (myisam, innodb)

also are there any other considerations I should think about???

Thanks a lot!!
ANdrew

Posted: Mon Jan 07, 2008 10:18 pm
by Christopher
The main considerations are whether you will do mainly reads or writes on the table, and whether you need transactions. Traditionally tables that do record locking have better write performance, whereas tables that do table locking have better read performance. But there are many exceptions to that. The manual goes through the tradeoffs.

Posted: Tue Jan 08, 2008 4:23 am
by Inkyskin
Personally, unless you are going to be performing a full text search on any of the tables, I'd go for innodb.

Posted: Tue Jan 08, 2008 10:54 am
by pickle
You're probably going to have more reads than writes, so MyISAM is probably best. But, as ~Inkyskin & I have just demonstrated, you're going to get mixed answer to this question.

There are some useful reviews of MyISAM vs. InnoDB found via Google.

Posted: Tue Jan 08, 2008 5:16 pm
by arpowers
what about the use of foreign keys? and row locking?

it seems like it would be nice to have these on a complex app...

the ability to do full text search is nice as well!

I don't know what to do...

Posted: Tue Jan 08, 2008 5:43 pm
by superdezign
Foreign keys aren't really necessary. You get more control at an application-level if you check there, and you can build error messages and such accordingly. arborint already touched on the row locking versus table locking, but if you're really curious, look in the MySQL manual. It has a good amount of resources on the different types on databases and the advantages / disadvantages of either.

If you're looking for a definite "this one is better," you're likely not going to find it. Just be the judge of what you're after, since you probably know your idea more than any of us could.

Posted: Wed Jan 09, 2008 4:55 am
by rsmarsha
Not sure on this but can you have a mix of table types in a database?

So innoDB for those that need transactional queries and myisam if you don't?

I've been reading about the 2 types recently as i've always used myisam, so it's a question in relation to the topic that might help the original poster when answered. ;)

Posted: Wed Jan 09, 2008 5:43 am
by Kieran Huggins
sure you can! All it affects is how that table is stored / accessed, so it's table-specific.

Posted: Wed Jan 09, 2008 1:36 pm
by arpowers
as always you guys are a lot of help....

this is the first place I come when I have a question!

Thanks a lot!

ap