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

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;

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.

Converting existing

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