Hi
I am migrating an Access database to MySQL and am trying to decide which table engine to use.

The main table of my database only has about 1000 records, but as this is a library database with companies and corresponding products and brands, being able to ensure referential integrity would be very useful.

Fulltext searching would be useful but not essential as I could create a full text search effectively through my asp page.
Transactions are not really necessary for such a small db and relatively few users.

Would it be worth using Innodb for such a small table? - or should I just try to enfore referential entigrity through coding in asp?
Any recommendations appreciated!


Always a good question

InnoDB supports constraints and row locking, and MyISAM doesn't.
MyIsam supports only table locking(?), but is fast.

From what you have said about having 1000 records, and few users, and it looks like you are mostle searching the database, I would say stick with MyIsam for speed.

If you have worries about multiple users updating information at the same time, or possible reference problems, use InnoDB.

Referential integrity (constraints) checking wont stop you adding data to the wrong customer, but it will make sure that if the record you add has dependencies on other records, that they exist first. You'll have to do this anyway in your program so that the constraints don't produce an error. It a bit of a double check. (Of course it can get more complicated than this.)

I think you can change the table types at a later date and add the constraints checking then with 'alter table' syntax.

It's also a bit of a matter of opinion too, so any other opinions ? (gje?)

b

thanks!

I think you are right, I should stick with myIssam.  I see what you mean about having to use code anyway to enforce referential integrity so that constraints don't produce errors. However I probably would have liked to use foreign key constraints to take advantage of cascade deletes.

I saw on the MySQL website that version 5.0 will support foreign keys in MyIssam tables. I wonder when the working version  will be released?!


Hi ... do I see that Custard is wondering about my opinion on this, and Lindy asking about release dates for MySQL 5?

Firstly, yes, you can change the table time later on easily enough if you need to.  I attended a seminar presented by Brian Aker (MySQL's director of development) a couple of weeks back, and he was talking about his choice MyIsam v InnoDB on the slashdot  web site where thraffic is huge ... cutting a long story short and NOT referring back to my notes they started with MyIsam, moved to InnoDB to give them record level locking because they were having problems with table locks neing far too wide on their busy site, then went back to MyIsam but with different table structures (they de-normalised) for more speed.   And the change of table types is "no great shakes" - provided that you use facilities that are available in both, at the very worst you can do a mysqldump and then restore having edited the "type=" line.   You would take a backup when doing such a change anyway, wouldn't you  

As regards the actual choice, I think either choice would be good / fine - you can have an excellent application whichever you use. There are times when you think long and hard about a decision and still can't think which way to go .... and it can be that the decision is an important one but the two alternatives are in balance, so it doesn't actually make a big difference and I think this decision is one of those.

Release of 5.0 to production level?   MySQL policy is to have release "levels" as follows:
* Prerelease for up to 1year
* alpha for 3-6 months
* beta 1-3 months
* gamma 1-3 months
* production 6-12 months (a.k.a. GA - Generally Available)
* retired
* end of life
HOWEVER they won't have two versions at Beta / Gamma at the same time.   What does this mean? At present, MySQL 5 is at Alpha and will probably there for a few months yet (meaning that in total it will be there much longer than their 6 month top target), if only to avoid two different versions both going Beta.   Then you're looking at a further, say, six months. So - production next summer?

Lindy, personally I would use MyIsam tables and write my own "cascaded delete" routine for my specific needs as a function / method / subroutine (what language are you using?) rather than waiting for a near-production release of MySQL5. Once the code is written and tested it can be reused and you'll have a full system up and running in the near term.

Caution My comments on release dates are as I noted them down / recall them from presentations by the MySQL team in October 2004 and do not constitute any official statement from them.  I have done my best to convery what they said correctly. End of Caution

---