MySQL Query: Counting duplicate values in a really huge table

Question

I have this MySQL table:

CREATE TABLE `triple` (
  `id_one` int(11) NOT NULL,
  `id_two` int(11) NOT NULL,
  `id_three` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

It coontains nearly 10 Million rows. The ids in the middle column ("id_two") can appear in different rows different times.

Short Example:

id_one    id_two    id_three
1         2         3
2         2         3
3         2         1
68        98        1
1         4         3
2         4         4
4         5         33
6         5         3
90        5         3
34        5         83
9         3         98

Now I want to count the different ids ("id_two"), that is in this example:

id_two     count
2     ->  3
98    ->  1
4     ->  2
5     ->  4
3     ->  1

How to achieve that efficiently? (It is a one time job...) That is the first thing. The second thing I need is: To count like above and then select only the ids, that have a count of more then let's say 100.

Thank you very much for helping!

Kind Regards Aufwind

Answer 1

The basic command to accomplish this is this one:

SELECT id_two, count(*) FROM triple GROUP BY id_two;

You can store that in a temporary table if you want to …

CREATE TEMPORARY TABLE xxx SELECT id_two, count(*) AS c FROM …
SELECT * FROM xxx WHERE c > 100;

… or use the result in an outer query …

SELECT * FROM (SELECT id_two, count(*) AS c FROM triple GROUP BY id_two) t WHERE c > 100;

… or use a HAVING-clause (as suggested by Marc in the comments):

SELECT id_two, count(*) AS c FROM triple GROUP BY id_two HAVING c > 100;

Answer 2

SELECT id_two, COUNT(*)
    FROM triple
    GROUP BY id_two
    HAVING COUNT(*) > 100