MySQL table type to use, innodb?

Question

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?

What are the characteristics of your database server? How much ram can you allocate to it? How's its disk performance? Are the updates you talk about above in transactions? — T.J. Crowder, Dec 28 '10 at 13:19
updates and selects are individual queries, not collated or bunched. just single line in php, 1 per page load. Does that mean I don't use transactions? Is table vs. row locking the biggest factor for this situation? — David19801, Dec 28 '10 at 14:50

score 2 · Answer 1 · answered Dec 28 '10 at 13:21

2

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

answered Dec 28 '10 at 13:21

Costis Aivalis

13,680
3
46
47

What are transaction capabilities please? – David19801 Dec 28 '10 at 13:24
The ability to define units of updates that either are committed altogether or rolled back in case something goes wrong. This way your data stays consistent. – Costis Aivalis Dec 28 '10 at 15:04

score 2 · Answer 2 · answered Dec 28 '10 at 13:23

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).

score 1 · Accepted Answer · answered Dec 28 '10 at 15:08

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).

score 0 · Answer 4 · edited May 23 '17 at 12:11

0

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

edited May 23 '17 at 12:11

Community

1
1

answered Dec 28 '10 at 13:30

Nazariy

6,028
5
37
61

IMO fulltext search in MySQL is very bad. Lots of sacrifices to make. An external search seems to be the better solution in most cases. – DrColossos Dec 28 '10 at 13:33

MySQL table type to use, innodb?

4 Answers4