title: Update column from another table in large mysql db (7 million rows)
body: <h2>Description</h2>
  
  <p>I have 2 tables with the following structure (irrelevant columns removed):</p>
  
  <pre><code>mysql&gt; explain parts;
  +-------------+--------------+------+-----+---------+-------+
  | Field       | Type         | Null | Key | Default | Extra |
  +-------------+--------------+------+-----+---------+-------+
  | code        | varchar(32)  | NO   | PRI | NULL    |       |
  | slug        | varchar(255) | YES  |     | NULL    |       |
  | title       | varchar(64)  | YES  |     | NULL    |       |
  +-------------+--------------+------+-----+---------+-------+
  4 rows in set (0.00 sec)
  </code></pre>
  
  <p>and</p>
  
  <pre><code>mysql&gt; explain details;
  +-------------------+--------------+------+-----+---------+-------+
  | Field             | Type         | Null | Key | Default | Extra |
  +-------------------+--------------+------+-----+---------+-------+
  | sku               | varchar(32)  | NO   | PRI | NULL    |       |
  | description       | varchar(700) | YES  |     | NULL    |       |
  | part_code         | varchar(32)  | NO   | PRI |         |       |
  +-------------------+--------------+------+-----+---------+-------+
  3 rows in set (0.00 sec)
  </code></pre>
  
  <p>Table <code>parts</code> contains 184147 rows, and <code>details</code> contains 7278870 rows.
  The <code>part_code</code> column from <code>details</code> represents the <code>code</code> column from the <code>parts</code> table.
  Since these columns are <code>varchar</code>, I want to add the column <code>id int(11)</code> to <code>parts</code>, and <code>part_id int(11)</code> to <code>details</code>. I tried this:</p>
  
  <pre><code>mysql&gt; alter table parts drop primary key;
  Query OK, 184147 rows affected (0.66 sec)
  Records: 184147  Duplicates: 0  Warnings: 0
  
  mysql&gt; alter table parts add column
         id int(11) not null auto_increment primary key first;
  Query OK, 184147 rows affected (0.55 sec)
  Records: 184147  Duplicates: 0  Warnings: 0
  
  mysql&gt; select id, code from parts limit 5;
  +----+-------------------------+
  | id | code                    |
  +----+-------------------------+
  |  1 | Yhk0KqSMeLcfH1KEfykihQ2 |
  |  2 | IMl4iweZdmrBGvSUCtMCJA2 |
  |  3 | rAKZUDj1WOnbkX_8S8mNbw2 |
  |  4 | rV09rJ3X33-MPiNRcPTAwA2 |
  |  5 | LPyIa_M_TOZ8655u1Ls5mA2 |
  +----+-------------------------+
  5 rows in set (0.00 sec)
  </code></pre>
  
  <p>So now I have the id column with correct data in <code>parts</code> table. After adding <code>part_id</code> column to <code>details</code> table:</p>
  
  <pre><code>mysql&gt; alter table details add column part_id int(11) not null after part_code;
  Query OK, 7278870 rows affected (1 min 17.74 sec)
  Records: 7278870  Duplicates: 0  Warnings: 0
  </code></pre>
  
  <p>Now the big problem is how to update <code>part_id</code> accordingly? The following query:</p>
  
  <pre><code>mysql&gt; update details d
         join parts p on d.part_code = p.code
         set d.part_id = p.id;
  </code></pre>
  
  <p>was running for about 30 hours until I killed it.</p>
  
  <p>Note that both tables are MyISAM:</p>
  
  <pre><code>mysql&gt; select engine from information_schema.tables where table_schema = 'db_name' and (table_name = 'parts' or table_name = 'details');
  +--------+
  | ENGINE |
  +--------+
  | MyISAM |
  | MyISAM |
  +--------+
  2 rows in set (0.01 sec)
  </code></pre>
  
  <p>I just now realized that one of the problems was that dropping the key on <code>parts</code> table I dropped the index on the <code>code</code> column. On the other side, I have the following indexes on <code>details</code> table (some irrelevant columns are omitted):</p>
  
  <pre><code>mysql&gt; show indexes from details;
  +---------+------------+----------+--------------+-------------+-----------+-------------+------------+
  | Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Index_type |
  +---------+------------+----------+--------------+-------------+-----------+-------------+------------+
  | details |          0 | PRIMARY  |            1 | sku         | A         |        NULL | BTREE      |
  | details |          0 | PRIMARY  |            3 | part_code   | A         |     7278870 | BTREE      |
  +---------+------------+----------+--------------+-------------+-----------+-------------+------------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <h2>My questions are:</h2>
  
  <ol>
  <li>Is the update query OK or it can be optimized somehow?</li>
  <li>I will add the index on the <code>code</code> column in <code>parts</code> table, will the query run in a reasonable time, or it will run for days again?</li>
  <li>How can I make a (sql/bash/php) script so I can see the progress of the query execution?</li>
  </ol>
  
  <p>Thank you very much!</p>