title: Slow INSERT into InnoDB table with random PRIMARY KEY column's value
body: <p>For my website I use the PHP API for Flickr ( <a href="http://www.flickr.com/services/api/" rel="nofollow">http://www.flickr.com/services/api/</a> ). This API provides several useful methods to get photos around particular GPS positions.</p>
  
  <p>The call to API methods looks like URL with specific parameters like latitude, longitude, API key, radius, sorting, etc. Say, it'll look like <code>http://api.flickr.com/method?lat=0.0&amp;lon=0.0&amp;radius=10</code></p>
  
  <p>My website makes over 200,000 calls to API to generate several pages with pictures from Flickr. It is a pritty hard push on API thus I created a results cache in mySQL database.</p>
  
  <p>Simplified scheme of the InnoDB table with cache is:</p>
  
  <pre><code>char(32) request
  datetime expires // 2-3 days
  text     response // serialized data from API response
  </code></pre>
  
  <p>where <code>request</code> is a PRIMARY KEY and represents an MD5 hash of a request URI. Other fields are quite simple :)</p>
  
  <p>The problem arises when the table becomes large enough, say over 100,000 rows. New <code>INSERTs</code> take up to 2 seconds (and up to 6 (!) second with 1,000,000 rows).</p>
  
  <p>As far as I understand the problem is with PRIMARY INDEX and engine being InnoDB. Every time a new request is being inserted, InnoDB engine rebuilds the tree index and moves data around, because MD5(request) is a really random value.</p>
  
  <p>So... The question is whether there is a better way to cache such requests? Or maybe I should switch to MyISAM engine? Or may be I should try pseudo-partitioning and create several tables to solve the problem? Or may be just use not a BTREE but HASH index?</p>
  
  <p>Any ideas are welcome!</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>Ok, I tried to alter table as Furicane and Johan suggested, but still no luck - INSERTs takes up to 3 seconds. Currently <code>request</code> field became a normal non-unique index and new <code>id</code> column has been added as PRIMARY KEY with auto increment. Also I tried to add 4 partitions on this table with same results.</p>
  
  <p>I think that index on <code>request</code> field is still a bottleneck. The only way I currently see is to determine all possible parameters, add them as columns to a table and then create index on them.</p>
  
  <p>Any other ideas? :)</p>
  
  <p><strong>Edit 2:</strong></p>
  
  <p>Salman A in comments below said that his similar table performs much better (~0.03 for insert). This the problem may be in IO load on system. Though I cannot any high load on it.</p>
  
  <p><code>iostat</code> results:</p>
  
  <pre><code>avg-cpu:  %user   %nice %system %iowait  %steal   %idle
            22.94    0.71    8.42    8.50    0.00   59.43
  
  Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
  sda              38.01       151.04       114.32 1383655437 1047309046
  </code></pre>
  
  <p><code>iotop</code> results:</p>
  
  <pre><code>Total DISK READ: 152.91 K/s | Total DISK WRITE: 197.67 K/s
  </code></pre>
  
  <p>With mySQL on top of the list both for writing and reading. Maybe my disks are almost dead? How can I check disks performance?</p>