Re: Relational Integrity
Hi Roy, If this is a business application, don't go without transactions and foreign keys. Plain and simple. Use InnoDB. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com I need so general guidance on relational integrity. I'm setting up a reasonably small DB with 30 or so tables for a machine control application. Several of the tables will have referential links to each other (e.g. a finished part table will link to a master part type table via the product ID number). None of my table will ever contain more than a few hundred thousand records. This database is a conversion from an existing MS SQL7 system in which I made extensive use of foreign keys. SQL7 has worked out well in the past but Windows and VBNet has ceased to be an efficient machine control development environment. We have decided to migrate to Linux on all of our new systems where practical. My first stab at a MySQL implementation is to use the MyISAM table structure and not the InnoDB structure, foregoing the use of explicit foreign keys and letting my apps take care of the relational integrity. I gathered from reading DuBois that this is not an uncommon approach to a MySQL implementation. Question: Are the advantages of MyISAM tables vs. InnoDB tables sufficient for me to continue this approach or am I better off setting up InnoDB tables throughout? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relational Integrity
RAM is Cheap, so is a faster processor.. (InnoDB requires more RAM/Processor then the simpler MyISAM).. but your data and downtime is probably a lot more expensive. Its well worth it going with InnoDB. For most of what I do, I use a combination of InnoDB and HEAP Tables. On Tue, Jul 20, 2004 at 09:43:40AM +0200, Martijn Tonies ([EMAIL PROTECTED]) wrote: Hi Roy, If this is a business application, don't go without transactions and foreign keys. Plain and simple. Use InnoDB. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com I need so general guidance on relational integrity. I'm setting up a reasonably small DB with 30 or so tables for a machine control application. Several of the tables will have referential links to each other (e.g. a finished part table will link to a master part type table via the product ID number). None of my table will ever contain more than a few hundred thousand records. This database is a conversion from an existing MS SQL7 system in which I made extensive use of foreign keys. SQL7 has worked out well in the past but Windows and VBNet has ceased to be an efficient machine control development environment. We have decided to migrate to Linux on all of our new systems where practical. My first stab at a MySQL implementation is to use the MyISAM table structure and not the InnoDB structure, foregoing the use of explicit foreign keys and letting my apps take care of the relational integrity. I gathered from reading DuBois that this is not an uncommon approach to a MySQL implementation. Question: Are the advantages of MyISAM tables vs. InnoDB tables sufficient for me to continue this approach or am I better off setting up InnoDB tables throughout? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Singer X.J. Wang, Ph.D. Candidate Faculty of Computer Science, Dalhousie University 6050 University Avenue, Halifax, NS, Canada, B3H 1W5 Email:[EMAIL PROTECTED] Fax: (902) 492-1517 WWW: http://www.singerwang.com Schedule: http://schedule.singerwang.com --- Tobor, its Robot spelled backwards. - Product slogan for a toy called Tobor (circa 1978) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Relational Integrity
I need so general guidance on relational integrity. I'm setting up a reasonably small DB with 30 or so tables for a machine control application. Several of the tables will have referential links to each other (e.g. a finished part table will link to a master part type table via the product ID number). None of my table will ever contain more than a few hundred thousand records. This database is a conversion from an existing MS SQL7 system in which I made extensive use of foreign keys. SQL7 has worked out well in the past but Windows and VBNet has ceased to be an efficient machine control development environment. We have decided to migrate to Linux on all of our new systems where practical. My first stab at a MySQL implementation is to use the MyISAM table structure and not the InnoDB structure, foregoing the use of explicit foreign keys and letting my apps take care of the relational integrity. I gathered from reading DuBois that this is not an uncommon approach to a MySQL implementation. Question: Are the advantages of MyISAM tables vs. InnoDB tables sufficient for me to continue this approach or am I better off setting up InnoDB tables throughout? Thanks in advance for any advice. Sincerely, Roy Harrell Adaptive Equipment 2512 NE 1st Blvd #400 Gainesville, FL 32609 352.372.7821 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Relational Integrity
MyISAM tables are sometimes faster than InnoDB, but for most applications the difference is going to be negligible. MyISAM tables also use less disk space (more compressed row format). These are the only 2 advantages I'm aware of. InnoDB on the other hand offers you foreign keys and transaction support at a small speed penalty. The other difference between the 2 is that MyISAM does table level locking, while InnoDB does row level locking. This means that while MyISAM is generally considered faster, it may actually turn out that InnoDB is faster for you if you have a high level of concurrency occuring and you end up with table lock contention between processes/threads in MyISAM. I switched to InnoDB about 8 months ago and have been very happy with it ever since. For me concurrency and table level locking where what prompted the switch. Foreign keys and transactions were just icing on the cake. For the application you describe I think you will do fine with either table type. John A. McCaskey -Original Message- From: Roy Harrell [mailto:[EMAIL PROTECTED] Sent: Monday, July 19, 2004 2:03 PM To: [EMAIL PROTECTED] Subject: Relational Integrity I need so general guidance on relational integrity. I'm setting up a reasonably small DB with 30 or so tables for a machine control application. Several of the tables will have referential links to each other (e.g. a finished part table will link to a master part type table via the product ID number). None of my table will ever contain more than a few hundred thousand records. This database is a conversion from an existing MS SQL7 system in which I made extensive use of foreign keys. SQL7 has worked out well in the past but Windows and VBNet has ceased to be an efficient machine control development environment. We have decided to migrate to Linux on all of our new systems where practical. My first stab at a MySQL implementation is to use the MyISAM table structure and not the InnoDB structure, foregoing the use of explicit foreign keys and letting my apps take care of the relational integrity. I gathered from reading DuBois that this is not an uncommon approach to a MySQL implementation. Question: Are the advantages of MyISAM tables vs. InnoDB tables sufficient for me to continue this approach or am I better off setting up InnoDB tables throughout? Thanks in advance for any advice. Sincerely, Roy Harrell Adaptive Equipment 2512 NE 1st Blvd #400 Gainesville, FL 32609 352.372.7821 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Relational Integrity
Roy, MyISAM tables also support the FULLTEXT index type, which may be useful, whereas InnoDB doesn't. Lachlan -Original Message- From: John McCaskey [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 July 2004 7:50 AM To: Roy Harrell; [EMAIL PROTECTED] Subject: RE: Relational Integrity MyISAM tables are sometimes faster than InnoDB, but for most applications the difference is going to be negligible. MyISAM tables also use less disk space (more compressed row format). These are the only 2 advantages I'm aware of. InnoDB on the other hand offers you foreign keys and transaction support at a small speed penalty. The other difference between the 2 is that MyISAM does table level locking, while InnoDB does row level locking. This means that while MyISAM is generally considered faster, it may actually turn out that InnoDB is faster for you if you have a high level of concurrency occuring and you end up with table lock contention between processes/threads in MyISAM. I switched to InnoDB about 8 months ago and have been very happy with it ever since. For me concurrency and table level locking where what prompted the switch. Foreign keys and transactions were just icing on the cake. For the application you describe I think you will do fine with either table type. John A. McCaskey -Original Message- From: Roy Harrell [mailto:[EMAIL PROTECTED] Sent: Monday, July 19, 2004 2:03 PM To: [EMAIL PROTECTED] Subject: Relational Integrity I need so general guidance on relational integrity. I'm setting up a reasonably small DB with 30 or so tables for a machine control application. Several of the tables will have referential links to each other (e.g. a finished part table will link to a master part type table via the product ID number). None of my table will ever contain more than a few hundred thousand records. This database is a conversion from an existing MS SQL7 system in which I made extensive use of foreign keys. SQL7 has worked out well in the past but Windows and VBNet has ceased to be an efficient machine control development environment. We have decided to migrate to Linux on all of our new systems where practical. My first stab at a MySQL implementation is to use the MyISAM table structure and not the InnoDB structure, foregoing the use of explicit foreign keys and letting my apps take care of the relational integrity. I gathered from reading DuBois that this is not an uncommon approach to a MySQL implementation. Question: Are the advantages of MyISAM tables vs. InnoDB tables sufficient for me to continue this approach or am I better off setting up InnoDB tables throughout? Thanks in advance for any advice. Sincerely, Roy Harrell Adaptive Equipment 2512 NE 1st Blvd #400 Gainesville, FL 32609 352.372.7821 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Relational Integrity
Oh I forgot to say, a full list of InnoDB restrictions is here: http://mysql.megalink.com/doc/mysql/en/InnoDB_restrictions.html -Original Message- From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 July 2004 9:36 AM To: [EMAIL PROTECTED] Subject: RE: Relational Integrity Roy, MyISAM tables also support the FULLTEXT index type, which may be useful, whereas InnoDB doesn't. Lachlan -Original Message- From: John McCaskey [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 July 2004 7:50 AM To: Roy Harrell; [EMAIL PROTECTED] Subject: RE: Relational Integrity MyISAM tables are sometimes faster than InnoDB, but for most applications the difference is going to be negligible. MyISAM tables also use less disk space (more compressed row format). These are the only 2 advantages I'm aware of. InnoDB on the other hand offers you foreign keys and transaction support at a small speed penalty. The other difference between the 2 is that MyISAM does table level locking, while InnoDB does row level locking. This means that while MyISAM is generally considered faster, it may actually turn out that InnoDB is faster for you if you have a high level of concurrency occuring and you end up with table lock contention between processes/threads in MyISAM. I switched to InnoDB about 8 months ago and have been very happy with it ever since. For me concurrency and table level locking where what prompted the switch. Foreign keys and transactions were just icing on the cake. For the application you describe I think you will do fine with either table type. John A. McCaskey -Original Message- From: Roy Harrell [mailto:[EMAIL PROTECTED] Sent: Monday, July 19, 2004 2:03 PM To: [EMAIL PROTECTED] Subject: Relational Integrity I need so general guidance on relational integrity. I'm setting up a reasonably small DB with 30 or so tables for a machine control application. Several of the tables will have referential links to each other (e.g. a finished part table will link to a master part type table via the product ID number). None of my table will ever contain more than a few hundred thousand records. This database is a conversion from an existing MS SQL7 system in which I made extensive use of foreign keys. SQL7 has worked out well in the past but Windows and VBNet has ceased to be an efficient machine control development environment. We have decided to migrate to Linux on all of our new systems where practical. My first stab at a MySQL implementation is to use the MyISAM table structure and not the InnoDB structure, foregoing the use of explicit foreign keys and letting my apps take care of the relational integrity. I gathered from reading DuBois that this is not an uncommon approach to a MySQL implementation. Question: Are the advantages of MyISAM tables vs. InnoDB tables sufficient for me to continue this approach or am I better off setting up InnoDB tables throughout? Thanks in advance for any advice. Sincerely, Roy Harrell Adaptive Equipment 2512 NE 1st Blvd #400 Gainesville, FL 32609 352.372.7821 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
