Mysql create table based on another table including indexes

I need to create a table based on another table in MYsql including the constraints and indices.

I have following scenario:

Table A- exists probably with millions of rows. I want to create table B with exactly same as table A (including constraints and indices). Process data from A a开发者_如何学Cnd some other source and insert to B. At the end of processing drop table A(drop indices associated with table A) and Rename table B to A including indices.

What is the best way to do this? Performance is my real concern.

Thanks

---

In cases like this, we assume you know the structure of the table. In other words, you are not asking "how do I find out what all of these columns, indexes and constraints are".

Second we tend to assume that all data in table A is valid, so you do not need to enforce constraints while copying from A to B.

Your "some other source" is a wildcard. I'm assuming you do not know if this other source contains valid data, and would suggest:

1) Create B w/o indeces or constraints

2) Copy/bulk insert from "other source" to B

3) Execute constraints by issuing SELECTS to find invalid rows. Skip this step if you know the data is valid. Once it is ok to proceed:

4) Copy A to B in "chunks". The issue here is that a straight SELECT...INTO... of all X millions of rows will take forever (because of explosion of resources required to do it in a single implied transaction), but a row-by-row will also take forever (because its just plain slow to do one row at a time). So you process chunks of 1000 or 10000 rows at a time.

5) When all data is copied over, add the indeces

6) Add the constraints

7) Drop A

8) Rename B