There is a more robust way if you running MySQL 5
Export this query using mysql client to an SQL script like this
mysql -h<-u<-p<-A -e"SELECT CNCAT('PTIMIZE TABLE ',table_schema,'.',table_name,';') FRM information_schema.tables WHERE ENGINE='InnoDB'"
Then run the script using mysql client.
Please remember, PTMIZE TABLE does absolutely nothing if all InnoDB data resides in the shared space.
Your must create all InnoDB tables as separate entities.
To do this, mysqldump all tables to a dump file.
Shutdown MySQL
add 'innodb_file_per_table' to my.cnf
Delete the ibdata files and the logs
Startup MySQL
Reload dump file.
Each InnoDB will reside in .frm and .ibd files
PTIMIZE will defragment each tablespace (.ibd) file
Message
From: "Chris White" <cwhite (AT) interfuel (DOT) com>
To: "Marten Lehmann" <lehmann (AT) cnm (DOT) de>
Cc: mysql (AT) lists (DOT) mysql.com
Sent: Tuesday, February 6, 2007 1:24:46 PM (GMT-0500) US/Eastern
Subject: Re: innodb madness
Marten Lehmann wrote:
How can I check which tables are using innodb with sql? How can walk through
the tables with "show databases" and "show tables". Thanks.
This somewhat depends on how the tables were declared. If you used
"ENGINE=InnoDb;" in the CREATE TABLE sequence, you'd be able to loop
through the results of "SHW TABLES" and run a "SHW CREATE TABLE" on
each of the resulting tables to get that answer.
While there is no REPAIR TABLE, a strange table locking issue we were
having yesterday was apparently solved by a run of PTIMIZE TABLE on the
table at hand. Why we're not sure yet, but things are working so can't
complain too much