database type....

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
User avatar
arpowers
Forum Commoner
Posts: 76
Joined: Sun Oct 14, 2007 10:05 pm
Location: san diego, ca

database type....

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

Post by Inkyskin »

Personally, unless you are going to be performing a full text search on any of the tables, I'd go for innodb.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
arpowers
Forum Commoner
Posts: 76
Joined: Sun Oct 14, 2007 10:05 pm
Location: san diego, ca

Post 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...
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post 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. ;)
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

sure you can! All it affects is how that table is stored / accessed, so it's table-specific.
User avatar
arpowers
Forum Commoner
Posts: 76
Joined: Sun Oct 14, 2007 10:05 pm
Location: san diego, ca

Post 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
Post Reply