InnoDB vs MyISAM using MySQL for a Social Networking Website
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
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
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
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
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.
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.
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
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.
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
Thanks a lot for your comments, guys!! It is a lot of help!
Best wishes,
David
Best wishes,
David
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Good summary, pytrin!
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
I do not agree (in general).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.
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
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
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.they should be business logic restrictions, not DB constraints
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
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
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
I said:
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.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.
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
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.
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
I am referring to :pytrin wrote:I said: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.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.
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)...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.
There are 10 types of people in this world, those who understand binary and those who don't
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
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.
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.
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
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.
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
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
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).
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).
Re: InnoDB vs MyISAM using MySQL for a Social Networking Website
Interesting - what's the new-school DB designpytrin 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.
Yeah, some call this "testing"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).
1. Think of client-side filtering/validation and server-side filtering/validationpytrin wrote:This means your logic is repeated twice, which I consider a waste and requires more system changes when the logic changes.
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.
No, I can't. It's not true!pytrin wrote:Also, you can make logical mistakes with the database exactly as you would with the application
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.
I must agree that keeping DB schemas on different production machines synchronized with the latest release one is a nasty thing to dopytrin 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'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