[] How to repeat tens of millions of tables

[Help] how to repeat tens of millions of tables? It has always been a waste of tens of thousands of small databases. I don't know how different indexes and data types will affect the efficiency. Didn't my password be leaked recently? The next step is to import the mysql database with a total of more than 20 million records, leaving only password fields, deleting all other fields, and performing tests such as select and insert. the efficiency of the select Index is significantly different, [help] how to repeat tens of millions of tables?
It has always been a waste of tens of thousands of small databases. I don't know how different indexes and data types will affect the efficiency. Didn't my password be leaked recently? The next step is to import the mysql database with a total of more than 20 million records, leaving only password fields, deleting all other fields, and performing tests such as select and insert. the efficiency of the select Index is significantly different, however, there is a problem in deduplication.

Method 1:
Create table newtable select distinct pwd FROM oldtable
This method seems to be the most efficient, but the machine is directly dragged to death during runtime, and the memory will be used up in a short time.

Method 2:
Retrieve records one by one and delete them again ($ num records are extracted each time, my $ num = 50)
$ Result = mysql_query ("select min (id), pwd FROM tablename WHERE id BETWEEN $ id AND $ num group by pwd ");
While ($ row = mysql_fetch_row ($ result )){
Mysql_query ("delete from tablename WHERE id> $ row [0] AND pwd = '$ row [1]'");
}
$ Id + = $ num;
Then, $ id is passed through the address bar or cookie. The efficiency is too low. after 100 minutes, more than 0.3 million duplicates are deleted.

What should I do to improve efficiency? Thank you.

------ Solution --------------------
How can I create a temporary table?
We recommend that you do this before, but you may not be able to hear it. it doesn't matter if you have a small amount of data.
Http://topic.csdn.net/u/20111225/22/7cabedc3-5e9e-42b3-b05b-153ba5a5a67f.html


Operations are required to occupy resources, which are inevitable ..... Unless you are happy to wait
------ Solution --------------------
2100 million. I don't know how efficient it is to add unique. try again.

SQL code

Alter ignore table mypwd add unique (pwd); alter table mypwd drop index pwd;
------ Solution --------------------
Use a temporary table. Create temporary table ....
------ Solution --------------------
Try:

Create a table and set unique fields.
Export the SQL file.
Re-import from source.
------ Solution --------------------
You can create only one click. Do not index. If the request is repeated, an error is reported and ignored.

If the select memory is not enough, you still need to save the disk. There are also distinct. repeated comparisons are required. There should be no source fast.
------ Solution --------------------

  
   Discussion
   

   
Reference:
   

   
You can create only one click. Do not index. If the request is repeated, an error is reported and ignored.
   

   
If the select memory is not enough, you still need to save the disk. There are also distinct. repeated comparisons are required. There should be no source fast.
   

   
Please refer to my reply on the 7th floor. if you do not create an index for the pwd field, the efficiency on the 7th floor will be very high and the processing will be completed in 110 seconds. Implemented in SQLyog