How to count the number of tags from 2 tables?

Question

MySQL

I have 3 tables
tag
id | name
tagpost
id | tag_id | post_id
tagoffers
id | tag_id | offer_id
How can I count how many times which tag is used?

SELECT tag.* , COUNT( tag.id ) as count
FROM  `tag` 
LEFT JOIN tagpost ON tag.id = tagpost.tag_id
LEFT JOIN tagoffers ON tag.id = tagoffers.tag_id
GROUP BY tag.id
ORDER BY COUNT( tag.id ) DESC

There is such a query, but it considers the largest dump from some table

DROP TABLE IF EXISTS `tag`;
CREATE TABLE IF NOT EXISTS `tag` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=493 ;

INSERT INTO `tag` (`id`, `name`) VALUES
(479, 'Электроник'),
(480, 'asas'),
(481, 'test'),
(482, 'зебра'),
(483, 'животные'),
(484, 'не дорого'),
(485, 'свадьба'),
(486, 'тамада'),
(487, 'привет'),
(488, 'тест'),
(489, 'платочек'),
(490, 'вихотка'),
(491, 'куплю'),
(492, 'cdflm');

DROP TABLE IF EXISTS `tagoffers`;
CREATE TABLE IF NOT EXISTS `tagoffers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_id` int(255) NOT NULL,
  `post_id` int(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

INSERT INTO `tagoffers` (`id`, `tag_id`, `post_id`) VALUES
(1, 485, 3),
(2, 486, 3),
(3, 485, 4),
(4, 486, 4),
(5, 487, 4),
(6, 488, 4),
(7, 489, 5),
(8, 484, 5);

DROP TABLE IF EXISTS `tagpost`;
CREATE TABLE IF NOT EXISTS `tagpost` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_id` int(255) NOT NULL,
  `post_id` int(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

INSERT INTO `tagpost` (`id`, `tag_id`, `post_id`) VALUES
(1, 480, 12),
(2, 481, 12),
(3, 480, 13),
(4, 481, 13),
(5, 480, 14),
(6, 481, 14),
(7, 482, 15),
(8, 483, 15),
(9, 484, 15),
(10, 490, 16),
(11, 491, 16),
(12, 492, 17),
(13, 485, 17);

Answer 1

SELECT 
  tag.id,
  tag.name,
  IFNULL(sum(tmp.cnt),0) as tag_cnt
FROM
tag 
LEFT JOIN
(
select tag_id, count(id) as cnt from tagpost group by tag_id
union all
select tag_id, count(id) as cnt from tagoffers group by tag_id
) as tmp on tag.id = tmp.tag_id
GROUP BY tag.id, tag.name