title: MySQL Query Questions with a Foreign Key Database
body: <p>Here's some background info. I have three MySQL tables (all InnoDB). The first table is used to store images records. The second table is used to store tags (to <strong>tag</strong> the images with. OMG). The third table is used to store relationships between the images and the tags. Yippee. Here's the structure of the tables.</p>
  
  <pre><code>CREATE TABLE `images` (
    `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
    `image` varchar(32) NOT NULL,
    `type` varchar(10) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB
  
  CREATE TABLE `tags` (
    `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
    `tag` varchar(64) NOT NULL,
    PRIMARY KEY (`id`) 
  ) ENGINE=InnoDB
  
  CREATE TABLE `images_to_tags` (
    `image_id` int(8) unsigned NOT NULL,
    `tag_id` int(8) unsigned NOT NULL,
    PRIMARY KEY (`image_id`,`tag_id`),
    KEY `tag_id` (`tag_id`),
    CONSTRAINT `images_to_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `images_to_tags_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB
  </code></pre>
  
  <p>The <em>type</em> field in the images database is either "image/gif", "image/png", or "image/jpeg".</p>
  
  <p>And there you have it. So my tables are happily populated with a quarter million images, 10,000 tags, and a metric shit load of image-tag relationships. </p>
  
  <p>1) I need to be able to count how many images have tags. Currently I am doing so with the following query: </p>
  
  <pre><code>SELECT COUNT(DISTINCT image_id) 
    FROM images_to_tags
  </code></pre>
  
  <p>Is this the most efficient way to do this? It seems kind of circuitous. Is this the only reasonable query which will achieve this purpose?</p>
  
  <p>2) For each <em>type</em> of image, I want to find out <strong>how many images are tagged</strong>. So say about 5,000 total images have tags, how would I know how many of those 5,000 tagged images are of <em>type</em> "image/png".  This isn't working for me:</p>
  
  <pre><code>SELECT COUNT(id), type 
    FROM images, 
         images_to_tags 
   WHERE images.id = images_to_tags.image_id 
  </code></pre>