Mysql – MyISAM and InnoDB tables in one database

innodbmyisamMySQL

I have a database with about 30 tables and 5 tables of them is write-intensive.

I'm considering

Convert 5 write-intensive tables to use InnoDB engine and keep the rest on MyISAM engine
Convert all tables to use InnoDB engine.

I wonder which approach is better?

To be more specific

The reason I want to keep some table on MyISAM engine is some of them has around 1,000,000 rows. I'm not sure how slower it will be for queries like "SELECT COUNT(*)" on these tables after converted to InnoDB.

I haven't done a test. I prefer getting some advices from any of you before start the switch.

Best Answer

These days, I always default to using InnoDB, especially on the write-intensive tables you mention where MyISAM suffers from full table locking. Here's a to-the-point comparison.

Reasons to use MyISAM:

Tables are really fast for select-heavy loads
Table level locks limit their scalability for write intensive multi-user environments.
Smallest disk space consumption
Fulltext index
Merged and compressed tables.

Reasons to use InnoDB:

ACID transactions
Row level locking
Consistent reads – allows you to reach excellent read write concurrency.
Primary key clustering – gives excellent performance in some cases.
Foreign key support.
Both index and data pages can be cached.
Automatic crash recovery – in case MySQL shutdown was unclean InnoDB tables will still - recover to the consistent state- No check / repair like MyISAM may require.
All updates have to pass through transactional engine in InnoDB, which often decreases - performance compared to non-transactional storage engines.

The above was taken from this site, which no longer seems to be working.

Related Solutions

Mysql – How to quickly rename a MySQL database (change schema name)

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.table TO new_db.table;

You will need to adjust the permissions after that.

For scripting in a shell, you can use either of the following:

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

Notes:

There is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.
If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:

mysqldump old_db | mysql new_db
If you have stored procedures, you can copy them afterwards:

mysqldump -R old_db | mysql new_db

Mysql – Setting up foreign keys in phpMyAdmin

If you want to use phpMyAdmin to set up relations, you have to do 2 things. First of all, you have to define an index on the foreign key column in the referring table (so foo_bar.foo_id, in your case). Then, go to relation view (in the referring table) and select the referred column (so in your case foo.id) and the on update and on delete actions.

I think foreign keys are useful if you have multiple tables linked to one another, in particular, your delete scripts will become very short if you set the referencing options correctly.

EDIT: Make sure both of the tables have the InnoDB engine selected.

Best Answer

Related Solutions

Mysql – How to quickly rename a MySQL database (change schema name)

Mysql – Setting up foreign keys in phpMyAdmin

Related Topic