MySQL rename and indexes

[Darrin Chandler]

On Sat, Nov 04, 2006 at 10:03:52AM -0700, Alex Dean wrote:
> '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

This has been true for at least 20 years and across a variety of
database servers. It's always worth keeping in mind. Often, the data to
import is sorted by one of the major keys. Inserting new data in key
order is a worst-case scenario for tree balancing. Even if the order is
random, the algo to build a balanced tree from scratch is still quicker
than insert/rebalance [, rinse, repeat].

-- 
Darrin Chandler            |  Phoenix BSD Users Group
dwchandler at stilyagin.com   |  http://bsd.phoenix.az.us/
http://www.stilyagin.com/  |