If you want to convert a MyISAM table to InnoDB, the process is fairly easy, but you can do something extra to speed things up. Before converting the table, adjust its order so that the primary key column is in order:
ALTER TABLE tablename ORDER BY 'primary_key_column';
This will pre-arrange the table so that it can be converted quickly without a lot of re-arranging required in MySQL. Then, simply change the table engine:
ALTER TABLE tablename ENGINE = INNODB;
If your table is large, then it may take a while to convert it over. There will probably be a fair amount of CPU usage and disk I/O in the process.
These statements are also safe in replicated environments. When you issue this statement to the master, it will begin the conversion process. Once it is complete on the master, the statement will roll down to the slaves, and they will begin the conversion as well. Keep in mind, however, that this can greatly reduce the performance of your configuration in the process.
Special thanks to Matthew Montgomery for the ORDER BY recommendation.
will the same trick for me to convert innodb to Myisam just by changing the ENGINE = INNODB to ENGINE = MYISAM .Do i have to make changes
MySIAM data files are stored arbitrary - its only the ibdata files that are stored sorted to the primary key. So this is pointless to do when converting from innodb to MyIASM.
In some cases, this statement does not work:
ALTER TABLE tablename ORDER BY 'primary_key_column';
Instead, use:
ALTER TABLE tablename ORDER BY primary_key_column;
(Note the removal of the single quotes)
Actually, those are NOT quotes, they are backticks, you know, the key next to the "1" on the common qwerty keyboard, and they are in fact correct.
How can I list what all the civicrm tablenames are in a database and have that list display the engine type as well?
Somehow, I have some of the tables with Engine Type INNODB, and other MyISAM. : (
When I try to manually change one of them, I get Error (121) with some text prior to it.
Thanks for the assist
Use this to batch generate the queries:
SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ORDER BY primary_key_column; ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;') FROM information_schema.tables WHERE TABLE_SCHEMA='my_database' AND ENGINE = 'MyISAM';
Replace 'my_database' with your DB name.
Originated from:
http://codesnippets.joyent.com/posts/show/1451
wojtha's query needs `` around table and db names in the output:
SELECT CONCAT('ALTER TABLE `',table_schema,'`.`',table_name,'` ORDER BY primary_key_column; ALTER TABLE `',table_schema,'`.`',table_name,'` engine=InnoDB;') FROM information_schema.tables WHERE TABLE_SCHEMA='xbmc-tv' AND ENGINE = 'MyISAM';
if you really want to get nuts you can use my favorite... just change the "webdb" string for your particular database or remove that whole line for them all... i use this to give me a list of the tables that have a primary key and do not have any full text fields (yes some people out there still dont put a PK in
)
select
TABLES.TABLE_SCHEMA as `Database`,
TABLES.table_name as `Table`,
columns.column_name as `Primary Key`,
CONCAT('ALTER TABLE `',TABLES.TABLE_SCHEMA,'`.`',TABLES.table_name,'` ORDER BY `',columns.column_name,'`; ALTER TABLE `',TABLES.TABLE_SCHEMA,'`.`',TABLES.table_name,'` engine=InnoDB;') as `Command`
from
information_schema.TABLES
left join information_schema.columns
on columns.table_name = TABLES.table_name
where
TABLES.ENGINE = "MyISAM"
and TABLES.TABLE_SCHEMA != "information_schema"
and TABLES.TABLE_SCHEMA != "mysql"
and columns.column_key = "PRI"
and TABLES.table_name = "webdb"
and concat(TABLES.TABLE_SCHEMA,".",TABLES.TABLE_NAME) not in
(
SELECT
concat(STATISTICS.TABLE_SCHEMA,".",STATISTICS.TABLE_NAME) as `Fulltable Name`
FROM
information_schema.STATISTICS
WHERE
STATISTICS.index_type = "FULLTEXT"
ORDER BY
1
)
order by
TABLES.TABLE_SCHEMA,
TABLES.table_name;