MySQL Query Questions with Foreign Key Database
Here is some background information. I have three MySQL tables (all InnoDB). The first table is used to store image records. The second table is used to store tags (to tag images using OMG). The third table is used to store the relationship between images and tags. Yippee. Here is the structure of the tables.
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
The type field in the image database is "image / gif", "image / png", or "image / jpeg".
And you have it. So my spreadsheets are happily filled with a quarter million images, 10,000 tags, and a metric load on image-tag relationships.
1) I need to be able to count how many images have tags. I am currently doing this with the following query:
SELECT COUNT(DISTINCT image_id)
FROM images_to_tags
Is this the most efficient way to do it? It seems to be cool. Is this the only reasonable request that will achieve this goal?
2) For each type of image, I want to know how many images are tagged . So say 5000 images have tags, how can I know how many of those 5000 tagged images are of type "image / png". This doesn't work for me:
SELECT COUNT(id), type
FROM images,
images_to_tags
WHERE images.id = images_to_tags.image_id
source to share
I think you are getting the number of images that have tags. If you want to count the number of tags for each type of image, you must make a score and group by type. try it
SELECT count(`id`), `type` FROM `images`, `images_to_tags` WHERE `images`.`id` = `images_to_tags`.`image_id` GROUP BY `images`.`type`
source to share