title: Optimizing CSV to MYSQL stored procedure
body: <p>I've tried this in PHP and now MYSQL and both outcomes seem to execute in around 15 seconds with 100 different items.  This procedure takes at CSV variable and inserts only the records that aren't currently in the database.  After this each item is inserted into the order_items_part_numbers tables with the corresponding order item id.  Here is the code I currently have.  Any help making this faster would be appreciated.  </p>
  
  <pre><code>PROCEDURE `add_part_number_to_item_mysql`(_order_items_id INT, _part_numbers VARCHAR(5000))
  BEGIN
  
  DECLARE _part_number VARCHAR(50);
  DECLARE _existing_part_number_id INT;
  
  WHILE _part_numbers IS NOT NULL DO
  SET _part_number = (SELECT CASE LOCATE(";", _part_numbers, 1)
   WHEN 0 THEN _part_numbers -- empty or single word
   ELSE SUBSTRING(_part_numbers, 1, LOCATE(";", _part_numbers, 1) - 1) -- multi-word
  END);
  
  SET _part_number = TRIM(_part_number);
  
  SET _existing_part_number_id = (SELECT ID FROM inventory WHERE item_number = _part_number);
  
  IF _existing_part_number_id IS NULL THEN
  INSERT INTO inventory (item_number) VALUES (UPPER(_part_number));
  SET _existing_part_number_id = @@IDENTITY;
  END IF;
  
  INSERT INTO order_items_part_numbers (order_items_id, inventory_id) VALUES (_order_items_id, _existing_part_number_id);
  
  
  SET _part_numbers = (SELECT CASE LOCATE(";", _part_numbers, 1)
   WHEN 0 THEN NULL -- empty or single word
   ELSE SUBSTRING(_part_numbers, LOCATE(";", _part_numbers)+1) -- multi-word
   END);
  
  SET _part_numbers = TRIM(_part_numbers);
  END WHILE;
  
  END 
  </code></pre>