title: long UPDATE query in MySQL
body: <p>I have to run some mysql update queries and they take quite a long time to run. I use a PHP script but each update takes between 40 and 600ms, generally around 250ms.</p>
  
  <p>I tried to optimize as much as I could but the script still takes more than 30 minutes to run (for 20000 entries in both tables).</p>
  
  <p>This is a part of my PHP script and the UPDATE query:</p>
  
  <pre><code>if (mysql_num_rows($Rmaster) == 1) {
  //Start time
      $Qchange_name = 'UPDATE slave SET NewAreaName = "'.$Amaster['AreaName'].'" WHERE Dialcode = "'.$Amain['Dialcode'].'" ORDER BY Dialcode ASC LIMIT 1 ';
      $Rchange_name = mysql_query($Qchange_name, $link);
  //end time = 40 to 600ms
  }
  </code></pre>
  
  <p>My SELECT queries are fast and I have an INDEX on Dialcode in both tables</p>
  
  <p>These are my tables (simplified) :</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `master` (
    `Dialcode` varchar(11) DEFAULT NULL,
    `AreaName` varchar(45) DEFAULT NULL,
    KEY `Dialcode` (`Dialcode`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  --
  -- Table structure for table `slave`
  --
  CREATE TABLE IF NOT EXISTS `slave` (
    `AreaName` varchar(47) DEFAULT NULL,
    `NewAreaName` varchar(47) NOT NULL,
    `Dialcode` varchar(15) DEFAULT NULL,
    KEY `Dialcode` (`Dialcode`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>Anything else I could try to make it faster?</p>
  
  <p>PHP: 5.3.4</p>
  
  <p>MySQL: 5.1.53</p>
  
  <p>(I will first try the PDO solution this weekend and update the post) thanks!</p>
  
  <p>UPDATE:
  I tried with PDO these 2 different queries:</p>
  
  <pre><code>$Qchange_name = $pdo-&gt;prepare('UPDATE slave SET NewAreaName = ? WHERE DIalcode = ?');
  $Qchange_name = $pdo-&gt;prepare('REPLACE INTO slave SET NewAreaName = ? AND Dialcode = ?');
  </code></pre>
  
  <p>But it takes around 18 seconds just to do the foreach for 150 rows!</p>
  
  <p>In my main loops I do:</p>
  
  <pre><code>$L4 = array(); // Before the loops
  else if ($countQmaster1 == 1) {
     $L4[] = array($Amaster1['AreaName'], $Amain['Dialcode']);        
  }
  </code></pre>
  
  <p>and then at the end of the loops:</p>
  
  <pre><code>foreach ($L4 as $b){
      $Qchange_name-&gt;execute($b); 
  }
  </code></pre>
  
  <p>Also I have Dialcode as PRIMARY INDEX</p>
  
  <p>Is there something I do wrong?</p>
  
  <p>UPDATE: Isolated the query.</p>
  
  <p>Here are the files to create the tables and the code. I isolated the query and reduced the script at the minimum but it still takes 17 seconds to run 150 rows.</p>
  
  <p>Do you see anything wrong? Do you get the same results?</p>
  
  <p>PHP code <a href="https://skydrive.live.com/redir.aspx?cid=9682137756f45f0f&amp;resid=9682137756F45F0F!526&amp;parid=9682137756F45F0F!168" rel="nofollow">here</a>
  or
  <a href="http://pastebin.com/bJjmLCeW" rel="nofollow">here</a></p>
  
  <p>Mysql tables to download <a href="http://pastebin.com/Z387VpcH" rel="nofollow">here</a> or
  <a href="https://skydrive.live.com/redir.aspx?cid=9682137756f45f0f&amp;resid=9682137756F45F0F!525&amp;parid=9682137756F45F0F!168" rel="nofollow">here</a></p>
  
  <p>I was thinking about a buffer problem? I'am running on my local machine and I get some fast and slow updates.</p>
  
  <pre><code>1 was executed in 38.87 ms 
  2 was executed in 33.05 ms 
  3 was executed in 33.17 ms 
  4 was executed in 91.42 ms 
  5 was executed in 36.17 ms 
  6 was executed in 30.23 ms 
  7 was executed in 33.15 ms 
  8 was executed in 33.21 ms 
  9 was executed in 33.22 ms 
  10 was executed in 42.72 ms 
  11 was executed in 32.12 ms 
  12 was executed in 33.04 ms 
  13 was executed in 33.14 ms 
  14 was executed in 33.2 ms 
  15 was executed in 33.2 ms 
  16 was executed in 33.16 ms 
  17 was executed in 33.25 ms 
  18 was executed in 33.2 ms 
  19 was executed in 33.12 ms 
  20 was executed in 33.25 ms 
  21 was executed in 33.15 ms 
  22 was executed in 33.21 ms 
  23 was executed in 33.16 ms 
  24 was executed in 41.54 ms 
  25 was executed in 74.79 ms 
  26 was executed in 129.35 ms 
  27 was executed in 77.65 ms 
  28 was executed in 34.06 ms 
  29 was executed in 33.21 ms 
  30 was executed in 33.2 ms 
  31 was executed in 33.17 ms 
  32 was executed in 140.5 ms 
  33 was executed in 34.04 ms 
  34 was executed in 41.53 ms 
  35 was executed in 33.23 ms 
  36 was executed in 33.14 ms 
  37 was executed in 158.06 ms 
  38 was executed in 149.8 ms 
  39 was executed in 278.14 ms 
  40 was executed in 154.52 ms 
  41 was executed in 149.71 ms 
  42 was executed in 91.45 ms 
  43 was executed in 274.55 ms 
  44 was executed in 149.77 ms 
  45 was executed in 278.46 ms 
  46 was executed in 1055.73 ms 
  47 was executed in 280.42 ms 
  48 was executed in 149.85 ms 
  49 was executed in 154.48 ms 
  50 was executed in 178.14 ms 
  51 was executed in 158.06 ms 
  52 was executed in 433.23 ms 
  53 was executed in 149.22 ms  
  54 was executed in 166.41 ms 
  55 was executed in 41.5 ms 
  56 was executed in 33.21 ms 
  57 was executed in 41.52 ms 
  58 was executed in 33.16 ms 
  59 was executed in 33.19 ms 
  60 was executed in 49.8 ms 
  61 was executed in 33.21 ms 
  62 was executed in 33.16 ms 
  63 was executed in 41.55 ms 
  64 was executed in 33.13 ms 
  65 was executed in 58.25 ms 
  66 was executed in 33.12 ms 
  67 was executed in 41.52 ms 
  68 was executed in 33.15 ms 
  69 was executed in 33.21 ms 
  70 was executed in 41.51 ms 
  71 was executed in 33.18 ms 
  72 was executed in 33.19 ms 
  73 was executed in 41.52 ms 
  74 was executed in 33.25 ms 
  75 was executed in 33.15 ms 
  76 was executed in 41.58 ms 
  77 was executed in 33.1 ms 
  78 was executed in 33.17 ms 
  79 was executed in 41.53 ms 
  80 was executed in 33.18 ms 
  81 was executed in 41.49 ms 
  82 was executed in 33.15 ms 
  83 was executed in 33.22 ms 
  84 was executed in 41.52 ms 
  85 was executed in 33.19 ms 
  86 was executed in 33.19 ms 
  87 was executed in 66.5 ms 
  88 was executed in 33.16 ms 
  89 was executed in 75.42 ms 
  90 was executed in 57.55 ms 
  91 was executed in 33.27 ms 
  92 was executed in 41.43 ms 
  93 was executed in 33.14 ms 
  94 was executed in 33.19 ms 
  95 was executed in 41.54 ms 
  96 was executed in 33.15 ms 
  97 was executed in 33.24 ms 
  98 was executed in 41.45 ms 
  99 was executed in 33.23 ms 
  100 was executed in 33.15 ms 
  101 was executed in 41.55 ms 
  102 was executed in 33.22 ms 
  103 was executed in 274.54 ms 
  104 was executed in 291.28 ms 
  105 was executed in 149.72 ms 
  106 was executed in 91.42 ms 
  107 was executed in 274.68 ms 
  108 was executed in 278.38 ms 
  109 was executed in 154.23 ms 
  110 was executed in 432.75 ms 
  111 was executed in 424.47 ms 
  112 was executed in 309.66 ms 
  113 was executed in 1101.32 ms 
  114 was executed in 327.63 ms 
  115 was executed in 116.23 ms 
  116 was executed in 34.24 ms 
  117 was executed in 33.18 ms 
  118 was executed in 65.39 ms 
  119 was executed in 34.36 ms 
  120 was executed in 30.48 ms 
  121 was executed in 35.85 ms 
  122 was executed in 33.18 ms 
  123 was executed in 41.54 ms 
  124 was executed in 33.13 ms 
  125 was executed in 33.23 ms 
  126 was executed in 41.49 ms 
  127 was executed in 83.13 ms 
  128 was executed in 33.13 ms 
  129 was executed in 41.58 ms 
  130 was executed in 33.15 ms 
  131 was executed in 33.17 ms 
  132 was executed in 41.5 ms 
  133 was executed in 33.18 ms 
  134 was executed in 41.54 ms 
  135 was executed in 33.13 ms 
  136 was executed in 33.19 ms 
  137 was executed in 41.55 ms 
  138 was executed in 33.14 ms 
  139 was executed in 33.21 ms 
  140 was executed in 41.5 ms 
  141 was executed in 33.22 ms 
  142 was executed in 33.17 ms 
  143 was executed in 41.51 ms 
  144 was executed in 33.13 ms 
  145 was executed in 33.22 ms 
  146 was executed in 41.51 ms 
  147 was executed in 75.34 ms 
  148 was executed in 39.76 ms 
  149 was executed in 34.38 ms  
  150 was executed in 33.12 ms 
  
  This script was executed in 13.45 seconds
  </code></pre>
  
  <p>This is the config:</p>
  
  <pre><code># The MySQL server
  [wampmysqld]
  port        = 3306
  socket      = /tmp/mysql.sock
  query_cache_size=32M
  # key_buffer = 16M
  max_allowed_packet = 1M
  table_cache = 64
  sort_buffer_size = 512K
  net_buffer_length = 8K
  read_buffer_size = 256K
  read_rnd_buffer_size = 512K
  myisam_sort_buffer_size = 8M
  </code></pre>
  
  <p>Edit: Solution</p>
  
  <p>Finally after changing ENGINE=InnoDB to MyISAM the query was executed in less than 3 seconds for 20000 rows!</p>
  
  <p>Thanks @JOHN for your help! PDO helped a lot!</p>