title: MySQL OUTER LEFT JOIN performance
body: <p>I am updating an existing web-based inventory system that pulls data from a MySQL database. The main structures for the data stored are "items" and "tags" with a one-to-many relationship (items can have multiple corresponding tags)</p>
  
  <p>The existing front-end system for the data is a Backbone.js app that pulls the entire datastore on login and manipulates that data in-memory, committing back to the database when necessary via a RESTful interface. (This is not how I would have designed the system, but it is now a common pattern in Backbone and Spine apps, and how most all of the tutorials and books teach these frameworks).</p>
  
  <p>To serve the initial fetch performed by the front-end in which it captures the entire dataset (about 1000 items and 10,000 item tags at this point) the back-end performs a SELECT query for the items table, and then subsequent SELECT queries for tags table for each item fetched. Performance sucks, obviously. I thought this could be improved with an JOIN, figuring one select query is better than 1000. The following query fetches the data I need but takes over 15s to execute even on my local development server. What gives? Can we improve this system or query without setting up additional infrastructure like a caching key-value store? </p>
  
  <pre><code>SELECT items.*, itemtags.id as `tag_id`, itemtags.tag, itemtags.type
  FROM items LEFT OUTER JOIN
       itemtags
       ON items.id = itemtags.item_id
  ORDER BY items.id;
  </code></pre>
  
  <p>Here are the table structures:</p>
  
  <pre><code>CREATE TABLE `items` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `num` int(11) NOT NULL,
    `title` varchar(100) NOT NULL,
    `length_inches` int(10) unsigned DEFAULT NULL,
    `length_feet` int(10) unsigned DEFAULT NULL,
    `width_inches` int(10) unsigned DEFAULT NULL,
    `width_feet` int(10) unsigned DEFAULT NULL,
    `height_inches` int(10) unsigned DEFAULT NULL,
    `height_feet` int(10) unsigned DEFAULT NULL,
    `depth_inches` int(10) unsigned DEFAULT NULL,
    `depth_feet` int(10) unsigned DEFAULT NULL,
    `retail_price` int(10) unsigned DEFAULT NULL,
    `discount` int(10) unsigned DEFAULT NULL,
    `decorator_price` int(10) unsigned DEFAULT NULL,
    `new_price` int(10) unsigned DEFAULT NULL,
    `sold` int(10) unsigned NOT NULL,
    `push_date` int(10) unsigned DEFAULT NULL,
    `updated` int(10) unsigned NOT NULL,
    `created` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=1747 DEFAULT CHARSET=latin1;
  
  CREATE TABLE `itemtags` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `item_id` int(10) unsigned NOT NULL,
    `tag` varchar(100) NOT NULL,
    `type` varchar(100) NOT NULL,
    `created` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=61474 DEFAULT CHARSET=latin1;
  </code></pre>