title: Mysql Update query optimize for single table
body: <p>I am using MySql database for my Project,</p>
  
  <p>I have one table having <strong>6 Millions records</strong>, Out of which I am updating around <strong>5 Millions records</strong>, my table structure and query are as below.</p>
  
  <p>Table </p>
  
  <pre><code>CREATE TABLE `temp` (
    `ref_id_1` int(11) NOT NULL,
    `ref_id_2` varchar(32) NOT NULL,
    `dna_id` int(11) DEFAULT NULL,
    `product_id` varchar(16) DEFAULT NULL,
    PRIMARY KEY (`ref_id_1`,`ref_id_2`),
    KEY `product_id` (`product_id`),
    KEY `dna_id` (`dna_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>Update Query</p>
  
  <pre><code>UPDATE temp SET dna_id = 8 WHERE product_id ='Dr_23' AND ref_id_1 = 4;
  </code></pre>
  
  <p>There are <strong>5 Million</strong> records for Product Id <strong>Dr_23</strong></p>
  
  <p>Above query taking around 2 minutes to execute. I have 32 GB RAM and SSD Harddisk.</p>
  
  <p>Does anyone know how to optimize this query </p>