view raw
CodeDump
N.F. N.F. - 11 months ago 29
MySQL Question

Eliminating Sub query in MySQL

I have a table in MySQL as below.

CREATE TABLE `myTable` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `employeeNumber` int(11) DEFAULT NULL,  

  `approveDate` date DEFAULT NULL,

  `documentNumber` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `INDEX_1` (`documentNumber`)

) ENGINE=InnoDB;


I want to make a query if a
documentNumber
is approved by all
employeeNumber
or approved by some of
employeeNumber
or not approved by any
employeeNumber
.

I made a query as below.

SELECT T1.documentNumber, 

 (CASE WHEN T2.currentNum = '0' THEN '1' WHEN T2.currentNum < T2.totalNum THEN '2' ELSE '3' END) AS approveStatusNumber

FROM myTable AS T1 LEFT JOIN

   (SELECT documentNumber, COUNT(*) AS totalNum, SUM(CASE WHEN approveDate IS NOT NULL THEN '1' ELSE '0' END) AS currentNum 

    FROM myTable GROUP by documentNumber) AS T2 ON T1.documentNumber = T2.documentNumber 

GROUP BY T1.documentNumber;


This SQL works, but very slow.
I tried
explain
on this SQL, the result is as below.

+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------------------+

| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |

+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------------------+

|  1 | PRIMARY     | T1         | range | INDEX_1       | INDEX_1 | 153     | NULL |   27 | Using where; Using temporary; Using filesort |

|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL | 5517 |                                              |

|  2 | DERIVED     | myTable    | index | NULL          | INDEX_1 | 153     | NULL | 5948 | Using where                                  |

+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------------------+


I think I have to eliminate sub query to improve my query.
How can I do the same thing without sub query? Or do I have another way to improve my query?

spencer7593 spencer7593
Answer

The expression to return currentNum could be more succinctly expressed (in MySQL) as 

  SUM(approveDate IS NOT NULL)

And there's no need for an inline view. This will return an equivalent result:

SELECT t.documentNumber
     , CASE 
         WHEN SUM(t.approveDate IS NOT NULL) = 0 
           THEN '1'
         WHEN SUM(t.approveDate IS NOT NULL) < COUNT(*)
           THEN '2'
         ELSE '3'
       END AS approveStatusNumber
  FROM myTable t
 GROUP BY t.documentNumber
 ORDER BY t.documentNumber
Share