MySQL rename and indexes

[Craig White]

On Fri, 2006-11-03 at 22:37 -0700, der.hans wrote:
> Am 03. Nov, 2006 schwätzte Alex Dean so:
> 
> >
> > On Nov 3, 2006, at 7:58 PM, Alex Dean wrote:
> >
> >>
> >> On Nov 3, 2006, at 6:35 PM, der.hans wrote:
> >>
> >>>> Any tips on creating a new table and indexes based on another
> >>>> table and
> >>>> its indexes?
> >>
> >> To create a new table based on an existing one, you can to this :
> >> "CREATE TABLE new_table LIKE old_table"
> >>
> >> This is just the column and index information.  If you also want
> >> the data, next do : "INSERT INTO new_table SELECT * FROM old_table"
> >
> > One caveat about that approach : The 'CREATE ... LIKE ...' will copy
> > indexes, but it won't automatically copy any foreign keys you've
> > defined.  You'd need to re-add them yourself.
> >
> > http://dev.mysql.com/doc/refman/5.0/en/create-table.html
> 
> What about the SHOW CREATE TABLE? Will that maintain everything about the
> table structure? We're importing a new data set and previous code does a
> "truncate table" then an import into the table. Since the report is live,
> I'd rather avoid just whacking the data and hoping we can get the new data
> in place...
> 
> CREATE TABLE `table_name` (
>    `stuff1` int(9) NOT NULL default '0',
>    `stuff2` char(1) NOT NULL default '',
>    `stuff3` date NOT NULL default '0000-00-00',
>    `stuff4` int(4) NOT NULL default '0',
>    PRIMARY KEY  (`stuff1`),
>    KEY `stuff2` (`stuff2`),
>    KEY `stuff3` (`stuff3`),
>    KEY `stuff4` (`stuff4`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
> 
> That all looks correct to me. SHOW INDEX comes up with the same indexes as
> the original table has.
----
ENGINE=InnoDB supports transactions, MyISAM does not. Transactions is
the all or none philosopy...

mysql database -u USER -p < some_file.sql

where some_file.sql (pseudo code)
start transaction
  drop table so_and_so
  create table so_and_so details
  import data
end transaction

It's faster to import without keys/indexes and create them after the
import if that matters but then you would want to drop the table, create
the table, import the data and then create the keys. MyISAM is supposed
to be faster than InnoDB

It's interesting to note the differences of the native client dump
routines of mysql and postgresql because postgresql data dump will
definitely not create the keys until the end.

All of the code necessary to re-create a table is contained in a 
'mysqldump database [table] -d' command

Craig