How important is the MySQL subsystem? For example, I need to synchronize two databases called events . Those. do not even synchronize, but make sure that the second database has a structure similar to the first one, and the data is from the second database. And when comparing schemas in SQLyog (the Schema Syncronyzation Tool tool), I see that the differences in the table structures of these databases are only in the subsystems (in the first InnoDB , in the second MyISAM ):

 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; USE `events`; /* Alter table in target */ ALTER TABLE `eventlog` ENGINE=InnoDB; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; 

schema of the eventlog table of the first database:

 CREATE TABLE `eventlog` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `source` varchar(50) NOT NULL DEFAULT '', `date` date NOT NULL DEFAULT '0000-00-00', `time` time NOT NULL DEFAULT '00:00:00', `level` smallint(6) NOT NULL DEFAULT '0', `type` smallint(6) NOT NULL DEFAULT '0', `message` blob NOT NULL, `description` blob, `flag` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=cp1251 

Scheme of the eventlog table of the second database:

 CREATE TABLE `eventlog` ( `id` bigint(20) NOT NULL auto_increment, `source` varchar(50) NOT NULL default '', `date` date NOT NULL default '0000-00-00', `time` time NOT NULL default '00:00:00', `level` smallint(6) NOT NULL default '0', `type` smallint(6) NOT NULL default '0', `message` blob NOT NULL, `description` blob, `flag` tinyint(1) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=cp1251 

In this regard, I have a question, how will the change of the subsystem affect the application? Those. If the application used to work with the tables created in the MyISAM subsystem and starts working with the tables created in the InnoDB subsystem, will this affect the performance, security, etc.?

  • one
    It will also reflect how, MyISAM not a transaction engine, but InnoDB transactional one (if you record 10 values ​​in InnoDB , and there is an error on 10, then the changes can be rolled back, but you cannot do it with MyISAM ). And since in MyISAM there is no transaction, the database will work with it much faster, but there is no rollBack, but no transactions, which can lead to data loss. - MrFylypenko

1 answer 1

 MyISAM ΠΏΠΎΠ΄Π΄Π΅Ρ€ΠΆΠΈΠ²Π°Π΅Ρ‚ сТатиС Ρ‚Π°Π±Π»ΠΈΡ† Π² ΠΎΡ‚Π»ΠΈΡ‡ΠΈΠΈ ΠΎΡ‚ InnoDB. MyISAM ΠΈΠΌΠ΅Π΅Ρ‚ встроСнныС полнотСкстный поиск Π² ΠΎΡ‚Π»ΠΈΡ‡ΠΈΠΈ ΠΎΡ‚ InnoDB. InnoDB ΠΏΠΎΠ΄Π΄Π΅Ρ€ΠΆΠΈΠ²Π°Π΅Ρ‚ Ρ‚Ρ€Π°Π½Π·Π°ΠΊΡ†ΠΈΠΈ Π² ΠΎΡ‚Π»ΠΈΡ‡ΠΈΠΈ ΠΎΡ‚ MyISAM. InnoDB ΠΏΠΎΠ΄Π΄Π΅Ρ€ΠΆΠΈΠ²Π°Π΅Ρ‚ Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²ΠΊΠΈ уровня строки (MyISAM - Ρ‚ΠΎΠ»ΡŒΠΊΠΎ уровня Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹). InnoDB ΠΏΠΎΠ΄Π΄Π΅Ρ€ΠΆΠΈΠ²Π°Π΅Ρ‚ ограничСния Π²Π½Π΅ΡˆΠ½ΠΈΡ… ΠΊΠ»ΡŽΡ‡Π΅ΠΉ (MyISAM - Π½Π΅Ρ‚). InnoDB Π±ΠΎΠ»Π΅Π΅ Π½Π°Π΄Π΅ΠΆΠ½Π° ΠΏΡ€ΠΈ Π±ΠΎΠ»ΡŒΡˆΠΈΡ… ΠΎΠ±ΡŠΠ΅ΠΌΠ°Ρ… Π΄Π°Π½Π½Ρ‹Ρ…. InnoDB Π² Ρ‚Π΅ΠΎΡ€ΠΈΠΈ Π½Π΅ΠΌΠ½ΠΎΠ³ΠΎ быстрСС. 

I also advise http://itif.ru/otlichiya-myisam-innodb/

more details: http://irbis-school.com/blog/full/21/vybor-dvizhka-bd-innodb-vs-myisam/2

and some nuances https://www.opennet.ru/tips/1958_mysql_myisam_innodb.shtml

  • MyISAM showed a significantly higher speed than InnoDB - nick_n_a
  • Innodb has brought full-text search long ago. - Fine
  • what was highlighted relatively long ago, newer ones may vary! therefore links are given! + Need to watch version SCL !!! - Vladimir Ch