Innodb vs myisam (table engines row lock vs table lock)
Innodb Default from MySQL 5.7 onwards.
Innodb designed for high-performance databases
Myisam : lack of transactions
MyISAM, is the lack of full table-level locking. This allows your queries to process faster.
Innodb row level locking but myisam full table level locking.
supporting foreign keys (RDBMS) and relationship constraints
Myisam not DBMS
Transactions log for auto recovery but myisam won’t.
SELECT TABLE_NAME, ENGINEFROM information_schema.
TABLES WHERE TABLE_SCHEMA = ‘database’ and ENGINE
Update with innodb
Change database tables storage engine in PHPmyadmin
ALTER TABLE wp_posts ENGINE=InnoDB;
Table locking vs row locking
When SQL gets update query (write /delete).
Total table locks up, other queries has to wait until
Write is done.
Reading not impacts (static sites).
Row level locks allows, other rowson a table
can perform actions
Like read, write simultaneously.
Reading locking tables faster than checking
rows & locking in large tables
default_storage_engine=InnoDB to the [mysqld] section of the system config file located at: /etc/my.cnf
applies only to new tables.
mysql -Bse ‘ALTER TABLE database_name.table_name ENGINE=MyISAM;’
mysql -Bse ‘ALTER TABLE database_name.table_name ENGINE=InnoDB;’
Convert MyISAM Tables To InnoDB
take a backup
‘SELECT CONCAT(“ALTER TABLE “,table_schema,”.”,table_name,” ENGINE=InnoDB;”) FROM information_schema.tables WHERE table_schema NOT IN (“mysql”,”information_schema”,”performance_schema”) AND Engine = “MyISAM”;’ | while read -r i; do echo $i; mysql -e “$i”; done | tee convert-to-innodb.log