mysql copy table

Continuing my chain of blatantly obvious mysql queries that I nonetheless didn't know at one stage :) copying a table isn't an obvious function in mySQL. So here's how to do a mysql copy table

Say you have a table called "tbl_theoriginal" which has the perfect, ideal, wonderful table structure for a new table you want "tbl_thenewone" and you would like to create it the query you would create is as follows:

CREATE tbl_thenewone SELECT * FROM tbl_the original

Simple huh! You can put in any WHERE clause you like and even a limit 1,1 or whatever if you don't want to transfer any content but just the table structure.

Comments

[Dan K]

Doesn't work. Indexes don't get copied. (Realized this when my queries were suddenly very s...l...o...w...)

[Tuomas Pulliainen]

I think it should be:
CREATE TABLE tbl_thenewone SELECT * FROM tbl_the original

You left out the "TABLE".