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
documentNumberemployeeNumberemployeeNumberemployeeNumberI 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+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| 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?
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
Source (Stackoverflow)