Bill,
Here's what I got running your example on Win2000, version 3.23.54-nt. As
you can see, it's very fast.
************
mysql> CREATE TABLE `projcomm` (
`PROJCOMM_ID` int(11) NOT NULL default '0',
`PROJECT_ID` int(11) NOT NULL default '-1',
`COMMUNTY_ID` int(11) NOT NULL default '0',
PRIMARY KEY (`PROJCOMM_ID`),
UNIQUE KEY `PROJECT_ID` (`PROJECT_ID`,`COMMUNTY_ID`),
KEY `COMMUNTY_ID` (`COMMUNTY_ID`)
) TYPE=MyISAM
;
Query OK, 0 rows affected (0.00 sec)
mysql> create table foo select * from projcomm where 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
*************
Do you have an older version of mysql you could try on your machine?
Note: The first time I tried the explicit create table it took 0.25 seconds,
but this was the first command I issued after launching the mysql client, so
I take that to be startup overhead associated with the first access to the
database files. The example above is representative of susbsequent queries.
--
Mike Ellis
Make Better Decisions: http://www.peertools.com
----- Original Message -----
From: "Bill Easton" <bill339@stripped>
To: <win32@stripped>
Sent: Wednesday, August 27, 2003 04:54
Subject: Cloning table takes forever
Sometimes, I want to create a new table with the same columns as an existing
table. I can do this with:
mysql> create table foo select * from projcomm where 0;
Query OK, 0 rows affected (0.77 sec)
Records: 0 Duplicates: 0 Warnings: 0
However, it takes nearly a second. If I create several, the delay becomes
noticeable.
I don't understand why. If I do a "show create table..." and then create
the table giving the structure in the create table statement, it only takes
0.02 sec. (See below.) I suppose I can write the code to do this, but I'd
rather not.
This happens on Windows 2000, MySQL 4.0.14-max-nt-log, and also on Windows
ME. It does not appear to happen on Linux.
Any hints as to why it takes so long?
----------
Here are the times when I give the structure explicitly. I'd think that the
"create ... select" would be doing the same thing.
mysql> select * from project where 0;
Empty set (0.00 sec)
mysql> show create table projcomm\G;
*************************** 1. row ***************************
Table: projcomm
Create Table: CREATE TABLE `projcomm` (
`PROJCOMM_ID` int(11) NOT NULL default '0',
`PROJECT_ID` int(11) NOT NULL default '-1',
`COMMUNTY_ID` int(11) NOT NULL default '0',
PRIMARY KEY (`PROJCOMM_ID`),
UNIQUE KEY `PROJECT_ID` (`PROJECT_ID`,`COMMUNTY_ID`),
KEY `COMMUNTY_ID` (`COMMUNTY_ID`)
) TYPE=MyISAM
1 row in set (0.00 sec)
mysql> create table foo (
-> `PROJCOMM_ID` int(11) NOT NULL default '0',
-> `PROJECT_ID` int(11) NOT NULL default '-1',
-> `COMMUNTY_ID` int(11) NOT NULL default '0');
Query OK, 0 rows affected (0.02 sec)
