Storage engine

[Andy-H]

Hi, was just wondering if anyone could help me with storage engines, I have done some researching but it seems that this is a grey-area that isn't looking likely for being resolved any time soon.

 

 

I have a database, with one particular table that will get 35k~ inserts/ day and be read from around 5-10k times / day, I was wondering which storage engine to use.

 

 

From what I read it was starting to look like InnoDB was a clear winner, due to row-level-locking, but then I read that MyISAM is faster? I then asked for advice in #mysql on freenode and was told "Use InnoDB, it's ACID compliant and supports x, y and z', but when I asked for the reasoning behind this all was quiet, and I didn't want to blindly follow advice without understanding why I was following it, or if I was even right in doing so.

 

 

P.S. It doesn't have to be MySQL, but that's what I know, so why introduce more complexity?

 

 

Also, what are peoples thoughts on persistent connections, as I currently spawn/close a connection on a per-request basis.

 

 

CHeers.

[Barand]

if it essential that you have referential integrity or you are are using transactions and need COMMIT/ROLLBACK facility then you need innoDB.

 

if you need FULLTEXT searches, you need MyIsam.

 

If none of the above then I'd go with MyIsam for its speed.

[Andy-H]

I don't really use FULLTEXT indexes, what are the advantages to it?

 

 

I don't need referential integrity and I was kind of hoping to use PDO transactions.

 

 

What about the InnoDB row level locking though? TO me that implies that when using MyIsam a table is not "full duplex" so to speak, so I can't read from a table during a right, I need my reads to be high priority whilst the writes are high volume. Would this still be a case for MyIsam?

[kicken]

I don't need referential integrity and I was kind of hoping to use PDO transactions.

 

If you want to do transactions you have to go with InnoDB, so that pretty much makes your choice for you.

 

 

What about the InnoDB row level locking though? TO me that implies that when using MyIsam a table is not "full duplex" so to speak, so I can't read from a table during a right,

 

MyISAM does support concurrent reads and inserts, but only under certain conditions.  Mainly that the table does not have any "gaps" in it's data area from row deletions.  So if all your doing is inserting/selecting/updating and not running any delete queries then you should be able to still read from the table while you have inserts going.  Otherwise if you are deleting stuff your selects may be blocked until there are no pending inserts or no more gaps in the data block.