How do I change the engine to MyISAM when using a SOURCE import in mysql? It currently defaults to InnoDB. would there be any difference between the speed of the import between the engines for say a 5gb .sql file?
A:
How do I change the engine to MyISAM when using a SOURCE import in mysql?
You could only edit a source file then. If an engine is explicitly specified for CREATE TABLE (and I bet it is) then it is the only solution.
It currently defaults to InnoDB. would there be any difference between the speed of the import between the engines for say a 5gb .sql file?
There should be no significant difference between speed of import to different storage engines, but you could save CPU cycles if you remove all indexes and recreate them right after you have imported all the data.
zerkms
2010-10-26 00:13:28
"you could save CPU cycles if you remove all indexes and recreate them right after you have imported all the data." -- this is actually not true for InnoDB. Unless you use the InnoDB plugin (5.1+, has to be enabled) adding an index recreates a new copy of the table and a new copy of each of the indexes.
Morgan Tocker
2010-10-27 19:49:56
@Morgan Tocker: really? Can you give some proof?
zerkms
2010-10-27 22:11:17
It's documented here - http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-create-index-overview.html - and many other places.
Morgan Tocker
2010-10-28 15:13:51
@Morgan Tocker: thanks. then my advice is useless?
zerkms
2010-10-28 23:02:56
The rest of it is fine (edit source file, then import). In InnoDB Plugin, your suggestion is very helpful. I've even filed a bug for mysqldump on it - http://bugs.mysql.com/bug.php?id=49120
Morgan Tocker
2010-10-28 23:47:42