Mysql group_concat of repeated keys and count of repetition of multiple columns in 1 query ( Query Optimization )

Question

This question is regarding query optimization to avoid multiple call to database via PHP.

So Here is scenario, I have two tables one contains information you can call this as reference table and another one is data table, fields key1 and key2 are common in both table, based on these fields, we can join them.

I don't know whether query can be made even simpler than what I am doing right now, what I want to achieve is as follows :

I would like to find distinct key1,key2,info1,info2 from main_info table, whenever serial value is less than 10 and key1,key2 of both table matches, and then group them by info1,info2, while grouping count the repeated key1,key2 for duplicates of info1,info2 fields and group_concat those keys

Contents of table main_info

MariaDB [demos]> select * from main_info;
+------+------+-------+-------+----------+
| key1 | key2 | info1 | info2 | date     |
+------+------+-------+-------+----------+
|    1 |    1 |    15 |    90 | 20120501 |
|    1 |    2 |    14 |    92 | 20120601 |
|    1 |    3 |    15 |    82 | 20120801 |
|    1 |    4 |    15 |    82 | 20120801 |
|    1 |    5 |    15 |    82 | 20120802 |
|    2 |    1 |    17 |    90 | 20130302 |
|    2 |    2 |    17 |    90 | 20130302 |
|    2 |    3 |    17 |    90 | 20130302 |
|    2 |    4 |    16 |    88 | 20130601 |
+------+------+-------+-------+----------+
9 rows in set (0.00 sec) 

Contents of table product1

MariaDB [demos]> select * from product1;
+------+------+--------+--------------+
| key1 | key2 | serial | product_data |
+------+------+--------+--------------+
|    1 |    1 |      0 | NaN          |
|    1 |    1 |      1 | NaN          |
|    1 |    1 |      2 | NaN          |
|    1 |    1 |      3 | NaN          |
|    1 |    2 |      0 | 12.556       |
|    1 |    2 |      1 | 13.335       |
|    1 |    3 |      1 | NaN          |
|    1 |    3 |      2 | 13.556       |
|    1 |    3 |      3 | 14.556       |
|    1 |    4 |      3 | NaN          |
|    1 |    5 |      3 | NaN          |
|    2 |    1 |      0 | 12.556       |
|    2 |    1 |      1 | 13.553       |
|    2 |    1 |      2 | NaN          |
|    2 |    2 |     12 | 129          |
|    2 |    3 |     22 | NaN          |
+------+------+--------+--------------+
16 rows in set (0.00 sec)

Via PHP I group fields info1 and info2 of table main_info, in current context serial,product_data of table product1, multiple times one after another (here I am running query twice as you can see)

For field serial - 1st query

MariaDB [demos]> select * , count(*) as serial_count,GROUP_CONCAT(key1,' ',key2) as serial_ids from 
    -> (
    -> SELECT distinct 
    -> if(b.serial  < 10,a.key1,null) AS `key1`,
    -> if(b.serial  < 10,a.key2,null) AS `key2`,
    -> if(b.serial  < 10,a.info1,null) AS `info1`, 
    ->         if(b.serial  < 10,a.info2,null) AS `info2`
    -> FROM main_info a inner join product1 b on  a.key1 = b.key1 AND a.key2= b.key2
    -> ) as sub group by info1,info2
    -> ;
+------+------+-------+-------+--------------+-------------+
| key1 | key2 | info1 | info2 | serial_count | serial_ids  |
+------+------+-------+-------+--------------+-------------+
| NULL | NULL |  NULL |  NULL |            1 | NULL        |
|    1 |    2 |    14 |    92 |            1 | 1 2         |
|    1 |    3 |    15 |    82 |            3 | 1 3,1 4,1 5 |
|    1 |    1 |    15 |    90 |            1 | 1 1         |
|    2 |    1 |    17 |    90 |            1 | 2 1         |
+------+------+-------+-------+--------------+-------------+
5 rows in set (0.00 sec)

For field product_data - 2nd query

MariaDB [demos]> select * , count(*) as product_data_count,GROUP_CONCAT(key1,' ',key2) as product_data_ids from 
    -> (
    -> SELECT distinct 
    -> if(b.product_data IS NOT NULL,a.key1,null) AS `key1`,
    -> if(b.product_data IS NOT NULL,a.key2,null) AS `key2`,
    -> if(b.product_data IS NOT NULL,a.info1,null) AS `info1`, 
    ->         if(b.product_data IS NOT NULL,a.info2,null) AS `info2`
    -> FROM main_info a inner join product1 b on  a.key1 = b.key1 AND a.key2= b.key2
    -> ) as sub group by info1,info2
    -> ;
+------+------+-------+-------+--------------------+------------------+
| key1 | key2 | info1 | info2 | product_data_count | product_data_ids |
+------+------+-------+-------+--------------------+------------------+
|    1 |    2 |    14 |    92 |                  1 | 1 2              |
|    1 |    3 |    15 |    82 |                  3 | 1 3,1 4,1 5      |
|    1 |    1 |    15 |    90 |                  1 | 1 1              |
|    2 |    2 |    17 |    90 |                  3 | 2 2,2 3,2 1      |
+------+------+-------+-------+--------------------+------------------+
4 rows in set (0.01 sec)

I would like to get output like this using one query, Group by info1, info2

+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| key1 | key2 | info1 | info2 | serial_count | serial_ids  | product_data_count | product_data_ids |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| NULL | NULL |  NULL |  NULL |            1 | NULL        |               NULL | NULL             |
|    1 |    2 |    14 |    92 |            1 | 1 2         |                  1 | 1 2              |
|    1 |    3 |    15 |    82 |            3 | 1 3,1 4,1 5 |                  3 | 1 3,1 4,1 5      |
|    1 |    1 |    15 |    90 |            1 | 1 1         |                  1 | 1 1              |
|    2 |    1 |    17 |    90 |            1 | 2 1         |                  3 | 2 2,2 3,2 1      |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+

Below is structure of tables

DROP TABLE IF EXISTS `main_info`;
CREATE TABLE `main_info` (
  `key1` int(11) NOT NULL,
  `key2` int(11) NOT NULL,
  `info1` int(11) NOT NULL,
  `info2` int(11) NOT NULL,
  `date` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


LOCK TABLES `main_info` WRITE;
INSERT INTO `main_info` VALUES (1,1,15,90,20120501),(1,2,14,92,20120601),(1,3,15,82,20120801),(1,4,15,82,20120801),(1,5,15,82,20120802),(2,1,17,90,20130302),(2,2,17,90,20130302),(2,3,17,90,20130302),(2,4,16,88,20130601);
UNLOCK TABLES;


DROP TABLE IF EXISTS `product1`;
CREATE TABLE `product1` (
  `key1` int(11) NOT NULL,
  `key2` int(11) NOT NULL,
  `serial` int(11) NOT NULL,
  `product_data` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


LOCK TABLES `product1` WRITE;
INSERT INTO `product1` VALUES (1,1,0,'NaN'),(1,1,1,'NaN'),(1,1,2,'NaN'),(1,1,3,'NaN'),(1,2,0,'12.556'),(1,2,1,'13.335'),(1,3,1,'NaN'),(1,3,2,'13.556'),(1,3,3,'14.556'),(1,4,3,'NaN'),(1,5,3,'NaN'),(2,1,0,'12.556'),(2,1,1,'13.553'),(2,1,2,'NaN'),(2,2,12,'129'),(2,3,22,'NaN');
UNLOCK TABLES;

Someone please help me to get result in one query.

Answer 1

How about combining your two queries with a JOIN?

SQL:

 SELECT
    tbl1.key1, tbl1.key2, tbl1.info1, tbl1.info2, tbl1.serial_count, tbl1.serial_ids,
    tbl2.product_data_count, tbl2.product_data_ids
 FROM 
 (
select * , count(*) as serial_count,GROUP_CONCAT(key1,' ',key2) as serial_ids from 
 (
 SELECT distinct 
 if(b.serial  < 10,a.key1,null) AS `key1`,
 if(b.serial  < 10,a.key2,null) AS `key2`,
 if(b.serial  < 10,a.info1,null) AS `info1`, 
         if(b.serial  < 10,a.info2,null) AS `info2`
 FROM main_info a inner join product1 b on  a.key1 = b.key1 AND a.key2= b.key2
 ) as sub group by info1,info2
 ) tbl1
 LEFT OUTER JOIN 
 (
select * , count(*) as product_data_count,GROUP_CONCAT(key1,' ',key2) as product_data_ids from 
 (
 SELECT distinct 
 if(b.product_data IS NOT NULL,a.key1,null) AS `key1`,
 if(b.product_data IS NOT NULL,a.key2,null) AS `key2`,
 if(b.product_data IS NOT NULL,a.info1,null) AS `info1`, 
         if(b.product_data IS NOT NULL,a.info2,null) AS `info2`
 FROM main_info a inner join product1 b on  a.key1 = b.key1 AND a.key2= b.key2
 ) as sub group by info1,info2
 ) tbl2
 ON tbl1.info1 = tbl2.info1 AND tbl1.info2 = tbl2.info2
 ORDER BY 3,4
 ;

Output:

mysql>  SELECT
    -> tbl1.key1, tbl1.key2, tbl1.info1, tbl1.info2, tbl1.serial_count, tbl1.serial_ids,
    -> tbl2.product_data_count, tbl2.product_data_ids
    ->  FROM
    ->  (
    -> select * , count(*) as serial_count,GROUP_CONCAT(key1,' ',key2) as serial_ids from
    ->  (
    ->  SELECT distinct
    ->  if(b.serial  < 10,a.key1,null) AS `key1`,
    ->  if(b.serial  < 10,a.key2,null) AS `key2`,
    ->  if(b.serial  < 10,a.info1,null) AS `info1`,
    ->          if(b.serial  < 10,a.info2,null) AS `info2`
    ->  FROM main_info a inner join product1 b on  a.key1 = b.key1 AND a.key2= b.key2
    ->  ) as sub group by info1,info2
    ->  ) tbl1
    ->  LEFT OUTER JOIN
    ->  (
    -> select * , count(*) as product_data_count,GROUP_CONCAT(key1,' ',key2) as product_data_ids from
    ->  (
    ->  SELECT distinct
    ->  if(b.product_data IS NOT NULL,a.key1,null) AS `key1`,
    ->  if(b.product_data IS NOT NULL,a.key2,null) AS `key2`,
    ->  if(b.product_data IS NOT NULL,a.info1,null) AS `info1`,
    ->          if(b.product_data IS NOT NULL,a.info2,null) AS `info2`
    ->  FROM main_info a inner join product1 b on  a.key1 = b.key1 AND a.key2= b.key2
    ->  ) as sub group by info1,info2
    ->  ) tbl2
    ->  ON tbl1.info1 = tbl2.info1 AND tbl1.info2 = tbl2.info2
    ->  ORDER BY 3,4
    ->  ;
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| key1 | key2 | info1 | info2 | serial_count | serial_ids  | product_data_count | product_data_ids |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
| NULL | NULL |  NULL |  NULL |            1 | NULL        |               NULL | NULL             |
|    1 |    2 |    14 |    92 |            1 | 1 2         |                  1 | 1 2              |
|    1 |    3 |    15 |    82 |            3 | 1 3,1 4,1 5 |                  3 | 1 3,1 4,1 5      |
|    1 |    1 |    15 |    90 |            1 | 1 1         |                  1 | 1 1              |
|    2 |    1 |    17 |    90 |            1 | 2 1         |                  3 | 2 2,2 3,2 1      |
+------+------+-------+-------+--------------+-------------+--------------------+------------------+
5 rows in set (0.01 sec)

mysql>  select version();
+-----------------+
| version()       |
+-----------------+
| 10.1.10-MariaDB |
+-----------------+
1 row in set (0.00 sec)