How do I CREATE TABLE form with indexes ?

[jeddiki]

Hi,

I am using :

CREATE TABLE T_update SELECT * FROM temp

to copy all data to the new T_update table. But I noticed that
my the indexes were not copied.

Apparently I can add them into the above code, but I am
not sure about the syntax

Is it like this ?

CREATE TABLE T_update  
KEY `id` (`id`),
KEY `start_date` (`start_date`),
PRIMARY KEY (cb_id) 
SELECT * FROM temp

Also what about the AUTO_INCREMENT column - cb_id ?

If it is auto_increment in the "temp" table will it be created that way in the
"T_update " table - and filled up with incrementing numbers ?

Thanks for any help on this one :)



.

[Atli]

Hey.

You can try the CREATE TABLE ... LIKE syntax. It should preserve indexes. Only downside I can see is that it doesn't preserve foreign key restraints.

[code=mysql]CREATE TABLE `tbl_new` LIKE `tbl`;
INSERT INTO `tbl_new` SELECT * FROM `tbl`;[/code]
(Note that these are two separate statements.)

[jeddiki]

Thanks for replying.

As the tables have 20,000 rows, I think this will take a lot longer
than doing the job with the CREATE FROM query - but only if I can get the indexes done as well.

One idea I had was to create the two standard indexes as part of the CREATE and then add a new column which would be the auto-increment primary key for the table. BUT using the extra ALTER TABLE query would only be necessary if it can not all be done with one CREATE FROM query.

If anyone knows how to write this in the correct syntax I would appreciate your input.


I currently have this:

$sql = "CREATE TABLE tbl_new  
KEY `id` (`id`),
KEY `start_date` (`start_date`),
PRIMARY KEY (cb_id) 
SELECT * FROM tbl";

Thanks


.

[Atli]

Ok, although 20,000 rows aren't really that much. Should only take a second to clone such a table using the syntax I suggested. (Unless it has an extraordinary amount of columns, of course.)

I tested it on my local server. Took a table with a PK, two text columns and a date column only, and with 21.005 rows, about 0.5 - 0.8 seconds to execute each statement. (Where both text columns were indexed.)

But anyhow...

If anyone knows how to write this in the correct syntax I would appreciate your input.

It's all explained in the manual.

In this case, it would go something like this:
[code=sql]CREATE TABLE `table_name`
(create table definitions...)
[table options]
[select statement][/code]
And note that if the select statement returns a column that is not defined in the create table definitions, it will simply be added. Which means you can define any special columns (auto_increment , primary key, indexes, etc...) in the create table definitions, but leave out those that need no special attention and have them created automatically.

Here you will also have to define the table options, such as the storage engine, the charset and the auto_increment position. (The LIKE clause in my previous suggestion takes care of that automatically, but this syntax does not.)

Your query is missing the parenthesis around the create table definitions (the KEY statements). Otherwise, besides the table options, it should work.

[jeddiki]

Well if it will only take a second or two then that is fine.

I will go with your two line suggestion:

1. CREATE TABLE `tbl_new` LIKE `tbl`;
2. INSERT INTO `tbl_new` SELECT * FROM `tbl`;

Thanks again.