innoDB vs myISAM

[hedidit]

Hi, I'm looking at what tables to use in my database and am thinking innodb will be better as it allows foreign keys however I was just wondering how much faster myISAM tables are?

My table will probably have around 500,000 records...

Any thoughts / advice?

Cheers

 

[TonyGroves]

MyISAM is reckoned to be faster than InnoDB, but what the margin is I don't know. Both systems can easily handle 500k records.

 

[rzs0502]

According to the Innodb site, this is faster that MyISAM
Check :

http://www.innodb.com/bench.php

Also look at the BENCHMARK() Function to do some tests on your setup: (search for benchmark)

http://www.samspublishing.com/articles/article.asp?p=394701&rl=1

"If you always do what you've always done, you will always be where you've always been."

 

[hedidit]

Thanks for the responses, I'll check out the link in a bit.

Following on, is it wise to store files (pdfs, jpgs etc) in mySQL fields? I'm moving from MS Access where this wasn't a good idea... the mySQL database will back a document management system which, at the moment, stores files in folders on a web server but it'd be better if mySQL could store them?

Thanks again

 

[TonyGroves]

You can store large amounts of binary data of any type in MySQL BLOB-type fields with no problems, though this obviously increases table sizes, which might reduce performance.

Regarding MyISAM versus InnoDB speed, I've no reason to doubt InnoDB's figures, but they are probably being very selective. Any comparisons I've ever seen have placed MyISAM ahead of InnoDB for speed.