title: Query on large i18n table slow in CakePHP
body: <p>The query below, using CakePHP's i18n table, takes around 1000ms to run.  My i18n table is large - it has around 600,000 rows.  I have the default indexes on this table, as per the Cake schema.</p>
  
  <p>How can I speed queries like this up?  I am caching them where possible but when the cache needs to be cleared these queries are run, and they slow down the performance quite noticeably.</p>
  
  <pre><code>SELECT `Category`.`id`, `Category`.`slug`, `Category`.`name`, `Category`.`description`, `Category`.`head_title`, `Category`.`display_title`, `Category`.`category_count`, `Category`.`product_count`, `Category`.`parent_id`, `Category`.`lft`, `Category`.`rght`, `Category`.`sort_order`, `Category`.`created`, `Category`.`modified`, `Category`.`image_processed`, ((rght - lft) = 1) AS `Category__is_child`, (`I18n__name`.`content`) AS `Category__i18n_name`, (`I18n__description`.`content`) AS `Category__i18n_description`, (`I18n__slug`.`content`) AS `Category__i18n_slug`, (`I18n__head_title`.`content`) AS `Category__i18n_head_title`, (`I18n__meta_description`.`content`) AS `Category__i18n_meta_description`, (`I18n__heading_title`.`content`) AS `Category__i18n_heading_title` FROM `fracmerl_dev`.`categories` AS `Category`
      INNER JOIN `fracmerl_dev`.`i18n` AS `I18n__name` 
          ON (`Category`.`id` = `I18n__name`.`foreign_key`
          AND `I18n__name`.`model` = 'Category' 
          AND `I18n__name`.`field` = 'name'
          AND `I18n__name`.`locale` = 'eng')
      INNER JOIN `fracmerl_dev`.`i18n` AS `I18n__description`
          ON (`Category`.`id` = `I18n__description`.`foreign_key`
          AND `I18n__description`.`model` = 'Category'
          AND `I18n__description`.`field` = 'description' 
          AND `I18n__description`.`locale` = 'eng')
      INNER JOIN `fracmerl_dev`.`i18n` AS `I18n__slug`
          ON (`Category`.`id` = `I18n__slug`.`foreign_key`
          AND `I18n__slug`.`model` = 'Category'
          AND `I18n__slug`.`field` = 'slug'
          AND `I18n__slug`.`locale` = 'eng')
      INNER JOIN `fracmerl_dev`.`i18n` AS `I18n__head_title`
          ON (`Category`.`id` = `I18n__head_title`.`foreign_key`
          AND `I18n__head_title`.`model` = 'Category'
          AND `I18n__head_title`.`field` = 'head_title'
          AND `I18n__head_title`.`locale` = 'eng')
      INNER JOIN `fracmerl_dev`.`i18n` AS `I18n__meta_description`
          ON (`Category`.`id` = `I18n__meta_description`.`foreign_key`
          AND `I18n__meta_description`.`model` = 'Category'
          AND `I18n__meta_description`.`field` = 'meta_description'
          AND `I18n__meta_description`.`locale` = 'eng')
      INNER JOIN `fracmerl_dev`.`i18n` AS `I18n__heading_title`
          ON (`Category`.`id` = `I18n__heading_title`.`foreign_key`
          AND `I18n__heading_title`.`model` = 'Category'
          AND `I18n__heading_title`.`field` = 'heading_title'
          AND `I18n__heading_title`.`locale` = 'eng')
  WHERE `I18n__slug`.`content` = 'category-slug'
  ORDER BY `Category`.`sort_order` ASC, `Category`.`name` ASC
  LIMIT 1
  </code></pre>
  
  <p>Edit - the indexes, the result of <code>SHOW INDEX FROM i18n</code> </p>
  
  <pre><code>i18n    0   PRIMARY 1   id  A   598455  NULL    NULL        BTREE
  i18n    1   locale  1   locale  A   5   NULL    NULL        BTREE
  i18n    1   model   1   model   A   3   NULL    NULL        BTREE
  i18n    1   row_id  1   foreign_key A   18701   NULL  NULL  BTREE
  i18n    1   field   1   field   A   9   NULL    NULL        BTREE
  </code></pre>