You get a bonus - 1 coin for daily activity. Now you have 1 coin

MySQL: the differences between MyISAM and InnoDB

Lecture



Description MyISAM InnoDB
Transactional engine? Transaction is a block of SQL statements that, in case of an error in one request, returns to the previous state (Rollback), and only if all the requests are executed is confirmed (Commit) Not Yes
Foreign key support Foreign keys are a way to link records in two tables across specific fields so that when you update a field in the parent, a certain change in the field in the child occurs automatically (you choose the child and the parent when you create the key; more precisely, you create the key in the child that refers to parent). Not Yes
Lock Row-level locking, i.e. if a process needs to update a row in a table, it only blocks this row, allowing others to update other rows in parallel Table level locking Row level lock
Simultaneous queries to different parts of the table. Slower Faster
With mixed load in the table (select / update / delete / insert) Slower Faster
Insert operation Faster Slower, because there is an overhead on a transaction, but this is the price of reliability
If read operations dominate (SELECT) Faster Slower
Deadlock Deadlock is a situation in a multitasking environment or a DBMS, in which several processes are in a state of endless waiting for resources captured by these processes themselves. Do not arise Are possible.
Full-text search support Yes No (available from MySQL version 5.6.4)
Request Count (*) Faster Slower
Mysqlhotcopy support The mysqlhotcopy utility is a Perl script that uses the LOCK TABLES, FLUSH TABLES SQL commands, and the cp or scp Unix utilities to quickly get a backup of the database. Yes Not
File storage of tables Each table has a separate file. Data at default settings is stored in large shared files.
Binary table copying? Tabular files can be moved between computers of different architectures and different operating systems without any conversion. Yes Not
The size of the tables in the database Less More
Behavior in case of failure The entire table is painted By logs, you can restore everything
In the case of storage "logs" and the like It is better Worse

Findings:

  • It is better to use MyISAM in tables that are dominated by one type of access: reading (news site) or writing (for example, logging);
  • Using InnoDB makes sense in all other cases and cases of increased requirements for data integrity.

Comments


To leave a comment
If you have any suggestion, idea, thanks or comment, feel free to write. We really value feedback and are glad to hear your opinion.
To reply

Databases - MySql (Maria DB)

Terms: Databases - MySql (Maria DB)