Best Mysql DB Engine When Insert update delete query is not made

Question

What should be the best Mysql db engine When query like Update, Insert, Delete are barely made (99% of time are not made)

The database has a lots entry mainly numeric data type, the select query are based on conditional statement which compares numeric value and return results

Should i use MyISAM here ?

One more thing is The database will be used by multiple user at the same time

thanks

Answer 1

http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/

InnoDB will outperform myiasm if you set to run the db in memory.. that is if you can fit it into memory.

Answer 2

I would go with MyISAM over InnoDB for three(3) major reasons

Reason # 1 : MyISAM storage engine provides simpler mechanisms for improving read performance

MyISAM tables are comprised of just three files

• .frm (Format File)
• .MYD (Data File)
• .MYI (Index File)

You can tune MyISAM tables for faster reads with one simple change to the table's row format.

For example, Pages 71-73 of the book MySQL Database Design and Tuning recommends doing this to a MyISAM table:

ALTER TABLE tblname ROW_FORMAT=Fixed;

This will convert all VARCHARs to CHARs internally. Ths will also make the MyISAM table about 50% larger but have increased read performance because the string length is more rigidly checked with VARCHAR whereas a CHAR's length is fixed.

As an alternative you could run myisampack on a MyISAM table to reduce the MyISAM table size and create a new read-only format.

These are one-time operations you can perform on any MyISAM table. Changing the row format on an InnoDB table is somewhat useless due to its clustered index layout (explained in Reason # 3)

Reason # 2 : MyISAM Caching is Simpler

MyISAM only caches index pages. InnoDB caches data and index pages. You can thus configure the MyISAM key cache smaller that an InnoDB Buffer Pool.

Reason # 3 : InnoDB carries too much baggage for protecting data

InnoDB features crash recovery on startup even if nothing is wrong (precautionary)

InnoDB always performs MVCC against rows of data. That creates extra housecleaning for a house that never get messy from INSERTs, UPDATEs, or DELETEs.

InnoDB performs double index lookups all the time, particularly when using non-UNIQUE indexes. The reason this happens is due to InnoDB's internal rowid index which is closely linked to the clustered index.

You could bypass having InnoDB startup by having the following in /etc/my.cnf

[mysqld]
skip-innodb

MySQL will thus restart much quicker. You could also delete /var/lib/mysql/ibdata1, /var/lib/mysql/ib_logfile0, and /var/lib/mysql/ib_logfile1.

CAVEAT

If the data is small enough, mount /var/lib/mysql on a RAM disk. Then, all bets are off for comparing MyISAM to InnoDB.