Eliminating Sub query in MySQL

Question

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?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

Eliminating Sub query in MySQL

Sign up