sergeidave Posted December 16, 2008 Share Posted December 16, 2008 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 Quote Link to comment Share on other sites More sharing options...
JonnoTheDev Posted December 16, 2008 Share Posted December 16, 2008 You use the JOIN syntax within your queries to relate fields. i.e. SELECT x,y,x FROM tableA a LEFT JOIN tableB b ON (a.id = b.fid) The relational fields should be given indexes. MyISAM is faster than INNODB so is used for most small/medium websites/applications. It really depends on what your database requirements are to decide what engine to use. I have only needed to use INNODB tables on large projects where tables may become corrupt due to a vast number of simultanious queries which would need repairing using MYISAM. Quote Link to comment Share on other sites More sharing options...
Mchl Posted December 16, 2008 Share Posted December 16, 2008 MyISAM is faster than INNODB ... in some cases. See this excellent blog entry for some benchmark results. Quote Link to comment Share on other sites More sharing options...
Maq Posted December 16, 2008 Share Posted December 16, 2008 I always thought InnoDB was specifically designed for transactional databases... Quote Link to comment Share on other sites More sharing options...
Mchl Posted December 16, 2008 Share Posted December 16, 2008 Even if it was true (which to my knowledge is not), what would stop us from using it in 'non-transactional' databases? InnoDB seems to be better choice over MyISAM in almost every manner (except for lack of FULLTEXT indexes, and perhaps a larger memory footprint - but you need to give lots of RAM to MySQL server anyways if you want it to work quick) Quote Link to comment Share on other sites More sharing options...
Maq Posted December 16, 2008 Share Posted December 16, 2008 Even if it was true (which to my knowledge is not), what would stop us from using it in 'non-transactional' databases? Nothing... Found this comparison (InnoDB vs MyISAM): I did some research after I posted this thread. Apparently MyISAM is faster than InnoDB. The only advantage InnoDB has over MyISAM is that it supports row locking, while MyISAM only supports table locking. Therefore, if lots of reads and writes are constantly being done to a very large table, it eliminates the constant database errors that using a MyISAM table would cause from the overload. InnoDB would therefore be a tad more reliable when you don't mind taking a small performance hit in exchange for not suffering from table locking issues. Quote Link to comment Share on other sites More sharing options...
Mchl Posted December 16, 2008 Share Posted December 16, 2008 1. The message at daniweb is from March 2006. Entry at MySQL Performance Blog is almost one year later. 2. Guys at MySQL Performace Blog did some benchmarking actually... and they're pros when it comes to MySQL consulting. Quote Link to comment Share on other sites More sharing options...
Maq Posted December 16, 2008 Share Posted December 16, 2008 It's the same information no matter where you go. Quote Link to comment Share on other sites More sharing options...
corbin Posted December 16, 2008 Share Posted December 16, 2008 MyISAM is faster than INNODB so is used for most small/medium websites/applications. It really depends on what your database requirements are to decide what engine to use. I have only needed to use INNODB tables on large projects where tables may become corrupt due to a vast number of simultanious queries which would need repairing using MYISAM. I always thought InnoDB was specifically designed for transactional databases... MyISAM locks entire tables instead of single rows like InnoDB does. So, anything that modifies data in a MyISAM causes a full table lock until the modifying is done. I've seen situations where a MyISAM table slows down an entire site to a crawl due to all the locking. Quote Link to comment Share on other sites More sharing options...
Mchl Posted December 16, 2008 Share Posted December 16, 2008 It's the same information no matter where you go. It's high time someone posted it to MythBusters. Quote Link to comment Share on other sites More sharing options...
Maq Posted December 16, 2008 Share Posted December 16, 2008 It's the same information no matter where you go. It's high time someone posted it to MythBusters. submit here Quote Link to comment Share on other sites More sharing options...
sergeidave Posted December 17, 2008 Author Share Posted December 17, 2008 Thanks a lot for your comments, guys!! I really appreciate it, it's a ton of help!! Best wishes, David Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.