title: Safely auto increment MySQL field based on MAX() subquery upon insert
body: <p>I have a table which contains a standard auto-incrementing ID, a type identifier, a number, and some other irrelevant fields. When I insert a new object into this table, the number should auto-increment based on the type identifier.</p>
  
  <p>Here is an example of how the output should look:</p>
  
  <pre><code>id      type_id     number
  1       1           1
  2       1           2
  3       2           1
  4       1           3   
  5       3           1
  6       3           2
  7       1           4
  8       2           2
  </code></pre>
  
  <p>As you can see, every time I insert a new object, the number increments according to the type_id (i.e. if I insert an object with type_id of 1 and there are 5 objects matching this type_id already, the number on the new object should be 6).</p>
  
  <p>I'm trying to find a performant way of doing this with huge concurrency. For example, there might be 300 inserts within the same second for the same type_id and they need to be handled sequentially.</p>
  
  <h2>Methods I've tried already:</h2>
  
  <h3>PHP</h3>
  
  <p>This was a bad idea but I've added it for completeness. A request was made to get the MAX() number for the item type and then add the number + 1 as part of an insert. This is quick but doesn't work concurrently as there could be 200 inserts between the request for MAX() and that particular insert leading to multiple objects with the same number and type_id.</p>
  
  <h3>Locking</h3>
  
  <p>Manually locking and unlocking the table before and after each insert in order to maintain the increment. This caused performance issues due to the number of concurrent inserts and because the table is constantly read from throughout the app.</p>
  
  <h3>Transaction with Subquery</h3>
  
  <p>This is how I'm currently doing it but it still causes massive performance issues:</p>
  
  <pre><code>START TRANSACTION;
  INSERT INTO objects (type_id,number) VALUES ($type_id, (SELECT COALESCE(MAX(number),0)+1 FROM objects WHERE type_id = $type_id FOR UPDATE));
  COMMIT;
  </code></pre>
  
  <p>Another negative thing about this approach is that I need to do a follow up query in order to get the number that was added (i.e. searching for an object with the $type_id ordered by number desc so I can see the number that was created - this is done based on a $user_id so it works but adds an extra query which I'd like to avoid)</p>
  
  <h3>Triggers</h3>
  
  <p>I looked into using a trigger in order to dynamically add the number upon insert but this wasn't performant as I need to perform a query on the table I'm inserting into (which isn't allowed so has to be within a subquery causing performance issues).</p>
  
  <h3>Grouped Auto-Increment</h3>
  
  <p>I've had a look at grouped auto-increment (so that the number would auto-increment based on type_id) but then I lose my auto-increment ID.</p>
  
  <hr>
  
  <p>Does anybody have any ideas on how I can make this performant at the level of concurrent inserts that I need? My table is currently InnoDB on MySQL 5.5</p>
  
  <p>Appreciate any help!</p>
  
  <p><strong>Update:</strong> Just in case it is relevant, the objects table has several million objects in it. Some of the <code>type_id</code> can have around 500,000 objects assigned to them.</p>