title: Selecting distinct values from a join of two large tables
body: <p>I have an <code>animals</code> table with about 3 million records. The table has, among a few other columns, an <code>id</code>, <code>name</code>, and <code>owner_id</code> column. I have an <code>animal_breeds</code> table with about 2.5 million records. The table only has an <code>animal_id</code> and <code>breed</code> column.</p>
  
  <p>I'm trying to find the distinct <code>breed</code> values that are associated with a specific <code>owner_id</code>, but the query is taking 20 seconds or so. Here's the query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT DISTINCT `breed`
  FROM `animal_breeds` 
  INNER JOIN `animals` ON `animals`.`id` = `animal_breeds`.`animal_id` 
  WHERE `animals`.`owner_id` = ? ;
  </code></pre>
  
  <p>The tables have all appropriate indices. I can't denormalize the table by adding a <code>breed</code> column to the <code>animals</code> table because it is possible for animals to be assigned multiple breeds. I also have this problem with a few other large tables that have one-to-many relationships.</p>
  
  <p>Is there a more performant way to achieve what I'm looking for? It seems like a pretty simple problem but I can't seem to figure out the best way to achieve this other than pre-calculating and caching the results.</p>
  
  <p>Here is the explain output from my query. Notice the <code>Using temporary</code></p>
  
  <pre><code>id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
  1   "SIMPLE"    "a" NULL    "ref"   "PRIMARY,animals_animal_id_index"   "animals_animal_id_index"   "153"   "const" 1126303 100.00  "Using index; Using temporary"
  1   "SIMPLE"    "ab"    NULL    "ref"   "animal_breeds_animal_id_breed_unique,animal_breeds_animal_id_index,animal_breeds_breed_index"  "animal_breeds_animal_id_breed_unique"  "5" "pedigreeonline.a.id"   1   100.00  "Using index"
  </code></pre>
  
  <p>And as requested, here are the create table statements (I left off a few unrelated columns and indices from the <code>animals</code> table). I believe the <code>animal_breeds_animal_id_index</code> index on <code>animal_breeds</code> table is redundant because of the unique key on the table, but we can ignore that for now as long as it's not causing the problem :)</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE `animals` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
    `owner_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `animals_animal_id_index` (`owner_id`,`id`),
    KEY `animals_name_index` (`name`),
  ) ENGINE=InnoDB AUTO_INCREMENT=2470843 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  
  
  CREATE TABLE `animal_breeds` (
    `animal_id` int(10) unsigned DEFAULT NULL,
    `breed` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    UNIQUE KEY `animal_breeds_animal_id_breed_unique` (`animal_id`,`breed`),
    KEY `animal_breeds_animal_id_index` (`animal_id`),
    KEY `animal_breeds_breed_index` (`breed`),
    CONSTRAINT `animal_breeds_animal_id_foreign` FOREIGN KEY (`animal_id`) REFERENCES `animals` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  </code></pre>
  
  <p>Any help would be appreciated. Thanks!</p>