What is the difference between the MySQL engines - InnoDB and MyISAM? What are their strengths and weaknesses?
4 answers
- MyISAM supports table compression as opposed to InnoDB.
- MyISAM has built-in full-text search, unlike InnoDB.
- InnoDB supports transactions in contrast to MyISAM.
- InnoDB supports row level locks (MyISAM is only table level).
- InnoDB supports foreign key constraints (MyISAM does not).
- InnoDB is more reliable with large amounts of data.
- InnoDB is a bit faster in theory.
AntonioK
2,759 ten 40
DennisBorisov DennisBorisov
449 four eleven
- I will add, InnoDB supports foreign keys. > InnoDB is a bit faster in theory. How did you get this? - KiTE
- In MySQL server version 3.23.44 and higher, InnoDB tables support checking for foreign key constraints. - DennisBorisov
|
Until recently, InnoDB did not support full-text indexes.
InnoDB does not store the number of rows in the table, nor the next value of the auto-increment field. The first is not so important, because very few people need to count all the rows in a table, without any filter whatsoever. But the second is fraught with theory. If the server is overloaded after deleting the last issued id, then the id will be re-allocated. However, I have not heard of such kinds of jambs in practice
Ipatiev Ipatiev
16.8k 3 17 44
|
There is one obvious (but not immediately) minus MyISAM arising from the peculiarities of locks. If heavy SELECTs can be executed in the system, then any UPDATE on the tables participating in it will wait for the end of the SELECT and block all further queries. If you work with the base quite actively, then this is not an option.
ilius33 ilius33
one
|
In short, InnoDB has transaction support, but MyISAM does not. And MyISAM , unlike InnoDB supports text indexing. Mainly used MyISAM .
alex_90 alex_90
791 6 21 63
|