InnoDB vs MyISAM using MySQL for a Social Networking Website

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

Moderator: General Moderators

sergeidave
Forum Newbie
Posts: 2
Joined: Fri Jun 06, 2008 1:07 pm

InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by sergeidave »

Hi,

I'm kind of new about using MySQL and databases in general. I want to practice web development by creating a website that will have social networking features. I'm positive that such a website will need a relational database, and if this is the case, I will need tables that relate to each other via foreign keys (is there another approach?).
So I'm wondering if MySQL is used by sooooooo many web developers, how come that the tables are defaulted to use MyISAM engines which do not support foreign keys? Are there any other methods of creating a relational database besides using foreign keys?
I'm sorry if I sound very novice (in fact I am novice), thank you so much for your help!!

Cheers,

David
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Re: InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by jmut »

I'd suggest to stick to InnoDB and use foreign keys, transactions etc.
Of course for tables where no relations is needed or no transactions required you can still use MyIsam engine.......mysql is perfectly working with mixture.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by Eran »

Foreign keys are not required to build a relational database, only for "forcing" the relationships. Foreign keys impose integrity on your data - but in my experience most relationships are better imposed through application code as they mostly do not conform to the simple restrictions of foreign keys.

There are other considerations between storage engines - MyISAM uses table-level locking vs. row-level locking in Innodb. Innodb supports transcations. MyISAM caches row counts, so count(*) is much faster than Innodb. MyISAM can have FULLTEXT indexes and so and so on. You better research a little about those storage engines if you are interested, but for simple application it would probably not make a difference.
sergeidave
Forum Newbie
Posts: 2
Joined: Fri Jun 06, 2008 1:07 pm

Re: InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by sergeidave »

Thanks a lot for your comments, guys!! It is a lot of help!

Best wishes,

David
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by califdon »

Good summary, pytrin!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by VladSun »

pytrin wrote:Foreign keys are not required to build a relational database, only for "forcing" the relationships. Foreign keys impose integrity on your data - but in my experience most relationships are better imposed through application code as they mostly do not conform to the simple restrictions of foreign keys.
I do not agree (in general).
You can have several applications (web based, desktop, services...) which use a single DB. So, the DB-side "forced relationships" will greatly help you to "force" the developers of all of these applications to use them and will keep your DB clear from "bogus records".
There are, indeed, restrictions defined in each application but they should be business logic restrictions, not DB constraints.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by Eran »

they should be business logic restrictions, not DB constraints
That's exactly my point. Foreign keys implement "hard" constraints, while in application code you can be much more flexible. I had many instances I had to dance around some particularly annoying database constraint that I was forced to work with instead of having it in the application code.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by VladSun »

A foreign key constraint means that the record doesn't make sense if there is no corresponding record. It doesn't depend on business logic, but on proper DB design.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by Eran »

I said:
but in my experience most relationships are better imposed through application code as they mostly do not conform to the simple restrictions of foreign keys.
In some cases there a foreign key might be put to good use. In my experience with web development, most of the time that is not the case.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by califdon »

I think we're getting into the religion of database design now. I sort of agree with both of you. I, too, have encountered instances where I found it necessary to depart from pure database design principles in order to get an application working within a reasonable time/effort/money, and after evaluating the potential problems that might arise. I am a strong proponent of proper design, normalization, etc. and you may have seen some of the tirades I've posted here. But a competent developer who understands the consequences of breaking the rules must sometimes do so in the name of practicality.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by VladSun »

pytrin wrote:I said:
but in my experience most relationships are better imposed through application code as they mostly do not conform to the simple restrictions of foreign keys.
In some cases there a foreign key might be put to good use. In my experience with web development, most of the time that is not the case.
I am referring to :
pytrin wrote:Foreign keys implement "hard" constraints, while in application code you can be much more flexible. I had many instances I had to dance around some particularly annoying database constraint that I was forced to work with instead of having it in the application code.
where you explain that foreign keys are "annoying database constraints", and I argue that if application logic doesn't conform DB constraints then there is something really wrong with one or both of the designs (app or db)...
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by Eran »

I iterated some experiences I had in which a database design was handed to me that included overuse of foreign keys constraints. Working with those was really annoying.

I didn't say that all foreign keys are annoying... and I don't consider blatant use of foreign keys "proper database design". Proper database design is using all the tools at your disposal in an appropriate manner.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by VladSun »

Still can't agree.

If one can use any of the constraints (foregn keys, unique etc.), one should use it. Also using transactions together with constraints is even better.

It will prevent some logical errors and bugs in one's application to write bogus records in DB and will raise an error which will be very helpful for tracking such issues.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by Eran »

That sounds like old-school database design. The database structure I mentioned before was designed by a former Oracle DBA (for many years), so I could understand where he came from.

In order to handle the errors you mentioned gracefully, the application needs to be aware of the those constraints in some way (otherwise a generic error message would be shown). This means your logic is repeated twice, which I consider a waste and requires more system changes when the logic changes.

Also, you can make logical mistakes with the database exactly as you would with the application, albeit changing constraints in the database is more difficult and harder to synchronize across environments (database schema revision is always a touchy subject).
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: InnoDB vs MyISAM using MySQL for a Social Networking Website

Post by VladSun »

pytrin wrote:That sounds like old-school database design. The database structure I mentioned before was designed by a former Oracle DBA (for many years), so I could understand where he came from.
Interesting - what's the new-school DB design ;)
pytrin wrote:In order to handle the errors you mentioned gracefully, the application needs to be aware of the those constraints in some way (otherwise a generic error message would be shown).
Yeah, some call this "testing" ;)
pytrin wrote:This means your logic is repeated twice, which I consider a waste and requires more system changes when the logic changes.
1. Think of client-side filtering/validation and server-side filtering/validation ;).
2. If you change so much of the logic then it's a bad design from the very beginning or user-requirements have not been collected properly and completely. In both cases refactoring would rarely work - you will need to redesign it and rewrite it.
pytrin wrote:Also, you can make logical mistakes with the database exactly as you would with the application
No, I can't. It's not true!
Also, a SQL schema create script file would usually contain < 1000 lines (with some SP defined < 2000-3000). Constraints definition lines number will be much less - lets say 10%. It is far far away from the number of code lines used in the application. So... calculate the possibility of errors.
pytrin wrote:albeit changing constraints in the database is more difficult and harder to synchronize across environments (database schema revision is always a touchy subject).
I must agree that keeping DB schemas on different production machines synchronized with the latest release one is a nasty thing to do :)
I've solved (almost) this problem by keeping track of SQL dump files into a CVS. After making a new release, CVS checkout and executing the SQL file is enough.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply