get many grouped values from mysql

Question

I have a table structure like this:

CREATE TABLE `test` (
  `a` tinyint(3) unsigned DEFAULT 0,
  `b` tinyint(3) unsigned DEFAULT 0,
   `c` tinyint(3) unsigned DEFAULT 0,
  `d` tinyint(3) unsigned DEFAULT 0,
  `e` tinyint(3) unsigned DEFAULT 0
  );

This has about 30 columns with some columns that have values from 0-200 (a,b) and some only have 5 values (0,1,2,3,4) (column c-d). There are aprox. 120k rows in the table.

To show the number of items per row I use a query for each column:

select a, count(*) FROM test group by a;
select b, count(*) FROM test group by b;
select c, count(*) FROM test group by c;
select d, count(*) FROM test group by d;
select e, count(*) FROM test group by e;

The problem with this is that it will fire 30 queries (one per column) and basically goes over the same set of data each time.

Is there a better way to do this?

I have tried with GROUP BY WITH ROLLUP but this results in a massive resultset which is slower to process than each individual query.

You can view a selection of the data on SQLfiddle: http://sqlfiddle.com/#!2/a9fd8/1

Answer 1

select 'a' as `column`, a as data, count(*) 
FROM test 
group by 'a', a
union
select 'b', b, count(*) 
FROM test 
group by 'b', b
union
select 'c', c, count(*) 
FROM test 
group by 'c', c
union
select 'd', d, count(*) 
FROM test 
group by 'd', d
union
select 'e', e, count(*) 
FROM test 
group by 'e', e

Don't know if it is any better but at least the planner will have a chance to optimize it.

Answer 2

Maybe something like this will work faster.

select qq, q, count(*) from
(
select 'a' qq, a q FROM test
union all select 'b' qq, b q FROM test
union all select 'c' qq, c q FROM test
union all select 'd' qq, d q FROM test
union all select 'e' qq, e q FROM test
) t
group by qq, q;

Answer 3

EDIT: this answer is completely off track

Try the following; it is a cleaner query, with just one pass, but I'm not sure how well it will perform due to the DISTINCT:

SELECT 
  COUNT(DISTINCT a) AS a,
  COUNT(DISTINCT b) AS b,
  COUNT(DISTINCT c) AS c,
  COUNT(DISTINCT d) AS d,
FROM
  t
;