MySQL rename and indexes

[Alex Dean]

On Nov 3, 2006, at 10:37 PM, der.hans wrote:

> 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 |

'SHOW CREATE TABLE' will display foreign keys if they do exist.   
'CREATE TABLE x LIKE y' won't.  Since you're using MyISAM tables,  
foreign keys are not supported so they're a non-issue.

I agree with Craig that if you've got a lot of data to import,  
suspending keys during an import will save a lot of time.  If you  
only have a few thousand records, you might not notice the  
difference, but with more data you'll probably get a significant  
speedup.

SHOW CREATE TABLE ...
CREATE TABLE new_table
ALTER TABLE new_table DISABLE KEYS
INSERT INTO new_table...
ALTER TABLE new_table ENABLE KEYS

> ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non- 
> unique indexes for a MyISAM  table. ALTER TABLE ... ENABLE KEYS  
> then should be used to re-create missing indexes. MySQL does this  
> with a special algorithm that is much faster than inserting keys  
> one by one, so disabling keys before performing bulk insert  
> operations should give a considerable speedup. Using ALTER  
> TABLE ... DISABLE KEYS requires the INDEX privilege in addition to  
> the privileges mentioned earlier.
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

As far as which table type is faster, it really depends on what the  
data is and how its used.  For a typical web application (mostly  
SELECTs), MyISAM will probably be faster.  InnoDB has a lot to  
recommend it, though.  Support for foreign keys, transactions, row- 
level locking, ACID compliance, automatic crash recovery, and other  
stuff.  Choosing which one is better for your application takes some  
investigation.  The two table types have different configuration  
parameters, so choosing one or the other (or more likely a mix of the  
two) will affect how you tune the server as well.

There are lots of articles online, and books in print, that cover the  
subject.  'High Performance MySQL' from O'Reilly is a good one.

alex
.