Prodigal Son Posted August 5, 2008 Share Posted August 5, 2008 I've recently read about when tables crash a MyISAM database engine can take a long time to repair, whereas InnoDB is really fast. I never even knew tables could crash lol. I originally chose MyISAM because most of my tables are using select queries (I'd say around 70% of my queries are selects). How often would a table crash? And how long would it take to repair, I assume it depends on the size of the table? If InnoDB is fast and reliable at fixing table crashes, why even bother using MyISAM? Or is a table crash a rare thing to happen? Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted August 5, 2008 Share Posted August 5, 2008 Tables shouldn't crash unless you are doing something to them to cause them to crash http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html Pick the engine for each table that best reflects its needs. Quote Link to comment Share on other sites More sharing options...
Prodigal Son Posted August 6, 2008 Author Share Posted August 6, 2008 Tables shouldn't crash unless you are doing something to them to cause them to crash http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html Pick the engine for each table that best reflects its needs. Yea I actually read that link earlier. I also read about how some people create copies of their tables. One would be a myisam table for selects while the copy would be innodb for updating and such. Is there any disadvantage to this approach? If you were to do that, how would it work? I.e. would you just name the tables like category_myisam and category_inno? And do you perform the copying? Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted August 6, 2008 Share Posted August 6, 2008 Yeah think about it If you update table B and select from table A then table A record's aren't upto date. Quote Link to comment Share on other sites More sharing options...
Prodigal Son Posted August 6, 2008 Author Share Posted August 6, 2008 Yeah think about it If you update table B and select from table A then table A record's aren't upto date. Yea, I'm not exactly sure how to implement that approach. I googled around and lots of people seem to suggest doing that, but no one really said HOW to do it lol. Anyone here have experience doing it that way? If it's possible, besides requiring more webspace, is there any disadvantage to doing that? Quote Link to comment Share on other sites More sharing options...
fenway Posted August 6, 2008 Share Posted August 6, 2008 Experience doing what? Quote Link to comment Share on other sites More sharing options...
Prodigal Son Posted August 6, 2008 Author Share Posted August 6, 2008 Experience doing what? Having 2 tables with the same data. The MyISAM table does the reading while the InnoDB does the writing? Quote Link to comment Share on other sites More sharing options...
fenway Posted August 6, 2008 Share Posted August 6, 2008 Experience doing what? Having 2 tables with the same data. The MyISAM table does the reading while the InnoDB does the writing? Assuming you're talking about completely unrelated tables, sure... if they're related, you're going to run into all sorts of issues. Quote Link to comment Share on other sites More sharing options...
Prodigal Son Posted August 7, 2008 Author Share Posted August 7, 2008 Assuming you're talking about completely unrelated tables, sure... if they're related, you're going to run into all sorts of issues. I mean the same table. I have a feeling no one knows what I'm talking about? I think I didn't explain it too well. I can show you what I mean if we are allowed to post links. It was talking about replication and how 'innodb would be the master and myisam would be the slave.' Quote Link to comment Share on other sites More sharing options...
fenway Posted August 7, 2008 Share Posted August 7, 2008 You can't have a single table be two things. Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted August 7, 2008 Share Posted August 7, 2008 No he is talking about abusing different engine types on the same table basically a clone of the same table so one can run update queries only the other run select But the problem I see is table A (the updater not MyISAM) isn't update to date with table B(another engine type better for updating queries) Quote Link to comment Share on other sites More sharing options...
Prodigal Son Posted August 7, 2008 Author Share Posted August 7, 2008 Here is a quote from a blog post: With replication it's even possible to take advantage of both storage engines on one table. For example, the master could store a table as InnoDB which makes it fast for INSERTs, UPDATEs and DELETEs while the slave(s) could store the same table as MyISAM and offer the best performance for SELECTs. Here is another person asking about it: http://forums.mysql.com/read.php?26,156318,156318#msg-156318 This site tells you how to implement it, but not too clear: http://crazytoon.com/2008/01/29/mysql-how-do-you-set-up-masterslave-replication-in-mysql-centos-rhel-fedora/ Well I guess its safe to say if you guys aren't too familiar with it, its probably not a common thing to do. When I first read it I just assumed it was one of those things that most people do, but I didn't lol. I think I don't need to do that for now, considering most people don't even do it right? And my tables are no way near huge status. I happened to read into that when I had the newbie problem of figuring out if I was supposed to be using MyISAM or InnoDB I guess I'll leave it at that for now. 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.