We help IT Professionals succeed at work.

MySQL Convert MyISAM TABLES to INNODB

Isisagate
Isisagate asked
on
3,011 Views
Last Modified: 2008-04-14
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.
Comment
Watch Question

Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Top Expert 2006

Commented:
(if you are using 4.0, you might need to use TYPE=InnoDB instead of ENGINE=InnoDB)
Top Expert 2006

Commented:
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.
Top Expert 2006

Commented:
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.
Top Expert 2006

Commented:
Thanks for the points!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.