Which way to go – which way to go…
Was speaking with a friend today about a question they had on how to copy a part of a table including the indexes. It had been a while since I had used those particular quirks of the CREATE statement so I could not recall how to do it off the top of my head (hate when that happens – bell rings but I can’t recall the exact tune.)
Anyway after a bit of quick digging we found CREATE TABLE … SELECT and CREATE TABLE … LIKE. Both can do the job (with a little help), just in different ways.
CREATE TABLE … SELECT will not automatically create any indexes for you, so if you want to have indexes in the created table, you should specify these *before* the SELECT statement. This means you will have to already know what the indexes are and how they are declared before issuing the command – but you can load the table at the same time.
CREATE TABLE … LIKE will create an *empty* table based on the definition of another table, including any column attributes and indexes defined in the original table. To load it, another command will have to be given (INSERT … SELECT).
Oh and before you ask – you can’t use both … syntax error 
So which way would you go – and why? Also if you can think of another way to do this (purely SQL) – feel free to educate me.
I think this is a good question. With no eloquent answer, it gives your brain something to chew on. Personally, I like CREATE TABLE LIKE. It seems less error prone, having MySQL copy over the indexes rather than having me re-type them all. And two smaller queries (instead of one giant CREATE TABLE SELECT with index definitions) would probably be easier for others to understand after I’m gone.
I agree, the CREATE TABLE LIKE seems to be the better way to go. It seems that it would be less of a maintenance problem as well. You won’t need to change the query if the Indexes change.