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 have only 5 values ​​(0,1,2,3,4) (column cd). There is aprox. 120k rows per table.

To display the number of elements in a 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 is that it will run 30 queries (one per column) and basically transfers the same data set every time.

Is there a better way to do this?

I tried with GROUP BY WITH ROLLUP, but this leads to a massive result set that is processed more slowly than every single request.

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

Answer 1

Perhaps something like this will work faster.

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

Answer 2

 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 

I do not know if this is better, but at least the scheduler will have the opportunity to optimize it.

Answer 3

EDIT : this answer is completely disabled

Try the following: this is a cleaner query, in a single pass, but I'm not sure how much it will work due to DISTINCT:

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

Answer 4

Nothing original, but you can try this one.

 SELECT t.col, t.val, tc FROM ( SELECT 'a' col, a val, count(*) c FROM test GROUP BY a UNION ALL SELECT 'b' col, b val, count(*) c FROM test GROUP BY b UNION ALL SELECT 'c' col, c val, count(*) c FROM test GROUP BY c UNION ALL SELECT 'd' col, d val, count(*) c FROM test GROUP BY d UNION ALL SELECT 'e' col, e val, count(*) c FROM test GROUP BY e ) t 

But if performance is a problem here, I would suggest the same thing that @edze suggested - index by columns (yes all 30). It will cost space, but will increase productivity. Or even create a view table

 CREATE TABLE `test_view` ( `col` char(1), `value` tinyint(3), `count` int ); 

for this task, and then just make a simple choice if it is performed frequently.

Answer 5

Depending on the environment, it may be more efficient to create a cumulative data table once, and then update it every time this table is modified. Your aggregate data table will have one row for each (current) value, and then 30 additional count columns. You can then enter the triggers in the original that update the counts. Naturally, it will slow down the write operations in the original table, although 30 indexes will be added this way.