SQL - Find all related tags?

I have a relation tag table like below. Relation tag_id "1" exists for all three node_id (1,2,3).

CREATE TABLE IF NOT EXISTS `relation` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tag_id` int(10) unsigned NOT NULL,
  `node_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ;

INSERT INTO `relation` (`id`, `tag_id`, `node_id`) VALUES
(1, 1, 1),
(2, 2, 1),
(3, 3, 1),
(4, 4, 1),
(5, 1, 2),
(6, 3, 2),
(7, 4, 2),
(8, 5, 2),
(9, 1, 3),
(10, 3, 3),
(11, 5, 3),
(12, 6, 3);

Using tag_id "1" as the starting point, how can I get a list of all tags ordered by number of occurrence? For example, the tag_id "3" exists in all three node_id records - while the tag_id 4 only exists twice (node_id 1 & 2).

The results should look like this:

tag_id  count
3       3
4       2
5       2
6       1
2       1

Update: Sorry, I didn't explain it good enough. When I said "tag_id "1" as the starting point", I meant if I searched for relations to tag_id - how could I order them by occurrence?

I want to find all tag_ids related to tag_id one ordered by count. This will allow me to see all the tag_id numbers used along with tag_id 1. Continuing with the example above - tag_id "3" should be the most used tag_id for node_ids with tag_id 1.

Update 2: I think I found an answer here.

SELECT tag_id, COUNT(*) as count FROM relation
WHERE tag_id != 1 AND node_id IN
(
    SELECT node_id FROM relation WHERE tag_id = 1
)
GROUP BY tag_id ORDER BY count DESC LIMIT 0,10

Is this method better than a LEFT JOIN? Is there any开发者_高级运维thing I can do to improve it's speed?

---

select tag_id, Count(*) as TagCount
from relation
where tag_id <> 1
group by tag_id
order by Count(*) desc

---

will this work?

SELECT tag_id, COUNT(*)
FROM relation
GROUP BY tag_id
ORDER BY COUNT(*) desc