MySQL table type to use, innodb?

innodb or myisam?

Heres what I have:

1 table,

ID (INT, auto-increment, primary key), name (char(40)), then 20 varchar(255)'s. 7 million rows.

Thats it, just the one table!

Query types: (everything is based on the id, only 1 row is ever returned or updated per query, theres no range searches or random or anything fancy)

SELECT (the 20 varchars) FROM table1 WHERE id='id';

UPDATE (the 20 varchars) FROM table1 WHERE 开发者_如何转开发id='id';

Deletes are done in bulk once a week, insertions maybe 5,000 per day. Theres an equal number of updates and selects (about 1000 per minute)

Unfortunately, I do not have the facility to benchmark at present, hence the question, which table type should I use for what I need? What are the main factors you'd use to choose in my case?

I think that InnoDB is the engine you need. Alone having transaction capabilities is the reason. The sizes of your tables justify this selection.

In short: MyISAM is faster but does not provide support for "transactions". INNODB provides support for transactions but this overhead can slow it down a tad bit.

However, note that INNODB out performs MyISAM for INSERT and UPDATES. So if you spend less time inserting/updating data and more time selecting it, MyISAM is preferable (and vice-versa).

Also note that MyISAM will lock the entire table when doing updates, whereas InnoDB will only lock the affected rows.

My personal philosophy is to use MyISAM only when I need full text search (and I agree with DrColossos that there are better search options).

In my opinion MyISAM use to be faster than InnoDB, now they are pretty much the same in speed. One of advantages of MyISAM over InnoDB is support of Full Text indexes it might be helpful if you planing to use full power of search queries. For other tasks I would recommend InnoDB, you can also use both on same database depending on your requirements.

There are many discussions on Stack OverFlow regarding MyISAM vs InnoDB, here is one of them