Re: MySQL rename and indexes

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
.



---------------------------------------------------
PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
To subscribe, unsubscribe, or to change you mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss