title: MySQL Sending Data Slow - LEFT JOIN Query
body: <p>I am currently performing a lookup against 2 tables using a <code>LEFT JOIN</code>. The transaction time is painfully slow, often ~10s. Please view the table/query/profile information below.</p>
  
  <p>Table #1 (14k rows):</p>
  
  <pre><code>CREATE TABLE `price` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `PRICE_ID` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `TYPE` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `PRICING_REF` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `STOCK_CODE` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `CALC_METHOD` bigint(20) DEFAULT NULL,
  `CALC_VALUE` double DEFAULT NULL,
  `STORED_PRICE` double DEFAULT NULL,
  `ROUNDER_METHOD` tinyint(4) DEFAULT NULL,
  `ROUNDER_DIRECTION` tinyint(4) DEFAULT NULL,
  `ROUNDER_MULTIPLE_OF` double DEFAULT NULL,
  `ROUNDER_ADJUSTMENT` double DEFAULT NULL,
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
  </code></pre>
  
  <p>Table #2 (18k rows):</p>
  
  <pre><code>CREATE TABLE `stock` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `STOCK_CODE` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
   `DESCRIPTION` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
   `SALES_PRICE` double DEFAULT NULL,
   `LAST_PURCHASE_PRICE` double DEFAULT NULL,
   `QTY_IN_STOCK` double DEFAULT NULL,
   `QTY_ON_ORDER` double DEFAULT NULL,
   `LAST_SALE_DATE` date DEFAULT NULL,
   PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
  </code></pre>
  
  <p>The query:</p>
  
  <pre><code>SELECT stock.*,price.stored_price,price.pricing_ref 
  FROM stock 
      LEFT JOIN price ON stock.stock_code = price.stock_code 
      AND price.pricing_ref = '2HWE001' 
  LIMIT 0,10
  </code></pre>
  
  <p>The profiler:</p>
  
  <pre><code>Detailed profile
  
  Order   StateDocumentation  Time
  1   Starting    102 µs
  2   Checking Permissions    5 µs
  3   Checking Permissions    3 µs
  4   Opening Tables  36 µs
  5   Init    35 µs
  6   System Lock 11 µs
  7   Optimizing  6 µs
  8   Statistics  24 µs
  9   Preparing   30 µs
  10  Executing   2 µs
  11  Sending Data    9.8 s
  12  End 19 µs
  13  Query End   8 µs
  14  Closing Tables  16 µs
  15  Freeing Items   67 µs
  16  Cleaning Up 16 µs
  </code></pre>
  
  <p>I have enabled query_cache in MySQL but it's yielding little benefit. Can anyone shed some light/experience on this?</p>