----- Original Message -----
> From: "Antonio Fernández Pérez"
> Subject: Re: Optimizing InnoDB tables
> I have enabled innodb_file_per_table (Its value is on).
> I don't have clear what I should to do ...
Then all new tables will be created in their own tablespace now. It's easy to convert an
existing table, too, simply do "alter table <yourtable> engine=innodb" - but that
will of course take a while on large tables.
The problem, however, is that there is no way to shrink the main tablespace afterwards.
Your tables will all be in their own space, but the ibdata1 will still be humoungous, even
though it's close to empty. Don't just delete it, btw, as it still contains metadata.
The only way to get rid of those, is to export ALL innodb tables, shut down mysqld, delete
all innodb files (iblog0/1, ibdata1 etc, but also <db>/*.ibd and the associated
<db>/*.frm files; then start the server (it'll recreate ibdata1 as specified in your
my.cnf, so shrink there, too, if required) and then import the lot again.
Note that, if you have the space, you don't *have* to do that - the huge ibdata1 file
doesn't do any harm; but do consider that as your dataset grows over the years, it'll
become more and more of a bother to actually do it.
Make sure you have backups when attempting :-)
Unhappiness is discouraged and will be corrected with kitten pictures.