MySQL Server
--
Questions
--
Followers
Top Experts
MySQL Convert MyISAM TABLES to INNODB
Is there a query I can run to convert a MyISAM Table to InnoDB retaining it's indexes, keys, and data without having to dump the data to a file, drop the table and recreate it.... Specifically we chose for a long insert process of a data conversion to use MyISAM to handle the data being inserted faster, and then want to convert back to InnoDB for it's speed in query results.
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
(if you are using 4.0, you might need to use TYPE=InnoDB instead of ENGINE=InnoDB)
Note also that you have it a bit backwards - MyISAM is faster for data-retrieval-skewed processing, whereas InnoDB is faster for mixed-use processing, where there are a number of INSERTs and UPDATEs along with SELECTs.
Here's some good information from MySQL's website on the subject of table types (http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html):
A general guideline could be as follows: if you require multi-statement transactions, advanced isolation levels and row-level locking, foreign key constraints, or otherwise have a requirement for ACID features, go for InnoDB. Otherwise, simply use MyISAM, the default.
If you have a message board application with lots of selects, inserts as well as updates, InnoDB is probably the generally appropriate choice for the actual message board tables.
The concurrency control mechanism used by MyISAM works very well with tables which are accessed mostly using SELECT statements, but also with concurrent INSERTs. Whether an INSERT can be concurrent depends on whether there is also free space in the table (from DELETEs, or in case of dynamic-row format also certain UPDATEs). MySQL will first fill the freespace, for space efficiency. If however, there is no freespace available, new rows are appended to the end of the physical table file, and this is when an INSERT can operate concurrently with SELECTs. UPDATE and DELETE statements always needs to issue an exclusive lock on a MyISAM table. Please note the trick mentioned earlier for dealing with DELETEs.
A general guideline could be as follows: if you require multi-statement transactions, advanced isolation levels and row-level locking, foreign key constraints, or otherwise have a requirement for ACID features, go for InnoDB. Otherwise, simply use MyISAM, the default.
If you have a message board application with lots of selects, inserts as well as updates, InnoDB is probably the generally appropriate choice for the actual message board tables.
The concurrency control mechanism used by MyISAM works very well with tables which are accessed mostly using SELECT statements, but also with concurrent INSERTs. Whether an INSERT can be concurrent depends on whether there is also free space in the table (from DELETEs, or in case of dynamic-row format also certain UPDATEs). MySQL will first fill the freespace, for space efficiency. If however, there is no freespace available, new rows are appended to the end of the physical table file, and this is when an INSERT can operate concurrently with SELECTs. UPDATE and DELETE statements always needs to issue an exclusive lock on a MyISAM table. Please note the trick mentioned earlier for dealing with DELETEs.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Thanks for the points!
MySQL Server
--
Questions
--
Followers
Top Experts
MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.