MySql create temporary table from source table without indexes

Question

I'm trying to "duplicate" a table from my db creating a temporary one. However I need indexes are not copied.

I'm using this query right now:

CREATE TEMPORARY TABLE temp365 LIKE contactlens;

but the result table contains also indexes. I checked out the documentation and I don't see a way to copy the structure without indexes. Because I can't have static name for indexes, I was wondering how I can drop all of them using simple SQL.

I was starting trying to avoid to copy them at all, but it seems not possible.

Answer 1

It's just to provide some examples to @wchiquito's comment.

It's pretty easy to create a copy of a table without indexes using CREATE TABLE ... SELECT command.

If you don't need to copy any rows from original table just provide a false value in WHERE clause or specify 0 in LIMIT one. Some examples:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] temp365 SELECT * FROM contactlens WHERE 0;

or a bit different way:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] temp365 SELECT * FROM contactlens LIMIT 0;

This way you'll get the same result as with CREATE TABLE ... LIKE, but without indexes.