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

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 »

Interesting - what's the new-school DB design
Agile design ;). What you are suggesting is more a waterfall approach - after the requirements have been finalized, you design the database and enforce the relationships via FK constraints.
Yeah, some call this "testing"
I see a difference between error handling and testing. I test my application code, I don't use it (application code) to test my database logic.
1. Think of client-side filtering/validation and server-side filtering/validation
The unfortunate truth is that due to security concerns validation is repeated on both client side and server side. But you're talking about something different. In applications where the UI and business logic interact directly (same language), this logic could easily be reused (and therefore less chance for mistakes). Not so when it's inside the database.
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.
I didn't say that so much of the logic changes, but nothing is sacred. I embrace an open and agile design style - I constantly refactor my code-base to best suit the changing requirements (requirements are never frozen. another unfortunate truth).
In order to be able to constantly refactor the code, I need the database to be flexible as well. For me this means, that if I use foreign keys, I will probably implement them near the end of the development cycle.
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.
What I meant is that logical errors could be induced anywhere, whether the logic is in the database or the application. So you can't rely on database logic as foolproof.

In the end I guess our different approaches result from different experiences with different projects and from a different development style.
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:In the end I guess our different approaches result from different experiences with different projects and from a different development style.
True. :)

Maybe it's because I work exclusively with PGSQL, not MySQL.
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 »

Probably ;)

Offtopic: from your experience, how does postgres compare to mysql? especially for high concurrency
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 »

I don't have high concurency issues in my web/desktop applications :)
You may find this interesting:
http://www.postgresql.org/docs/8.0/inte ... /mvcc.html

I don't want to start a new thread - there were a number of threads about PGSQL vs. MySQL recently.
IMHO PGSQL is closer to SQL standards, but that would be another flame to start ;)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply