|
I've wrote a function to add/subtract 'our' working days from a date:
CREATE DEFINER=`root`@`localhost` FUNCTION `DATEADD_BUSINESSDAYS`(`begin_date` DATE, `num_days` INT)
|
RETURNS date
|
LANGUAGE SQL
|
NOT DETERMINISTIC
|
READS SQL DATA
|
SQL SECURITY INVOKER
|
COMMENT 'Test function.... not yet in production'
|
BEGIN
|
DECLARE i, j INT;
|
IF (num_days = 0) THEN
|
RETURN begin_date;
|
END IF;
|
|
SET i = 0;
|
SET j = 0;
|
IF (num_days > 0) THEN
|
WHILE i < num_days DO
|
SET i = i + 1;
|
SET j = j + 1;
|
WHILE
|
DAYOFWEEK(DATE_ADD(begin_date, INTERVAL j DAY)) IN (1,7)
|
OR (SELECT 1 FROM holidays WHERE hDate = DATE_ADD(begin_date, INTERVAL j DAY))
|
DO
|
BEGIN
|
SET j = j + 1;
|
END;
|
END WHILE;
|
END WHILE;
|
ELSE
|
WHILE i > num_days DO
|
SET i = i - 1;
|
SET j = j - 1;
|
WHILE
|
DAYOFWEEK(DATE_ADD(begin_date, INTERVAL j DAY)) IN (1,7)
|
OR (SELECT 1 FROM holidays WHERE hDate = DATE_ADD(begin_date, INTERVAL j DAY))
|
DO
|
BEGIN
|
SET j = j - 1;
|
END;
|
END WHILE;
|
END WHILE;
|
END IF;
|
|
RETURN DATE_ADD(begin_date, INTERVAL j DAY);
|
END
|
Explain without function use:
EXPLAIN
|
SELECT
|
xxxx
|
FROM
|
invoices fac
|
INNER JOIN details det ON fac.Invoice = det.Invoice
|
WHERE
|
fac.`Data` = CURDATE()-1
|
AND fac.Invoice LIKE 'PVD%'
|
AND fac.Route IN ('038','205','211')
|
GROUP BY
|
fac.Client,
|
fac.DeliveryID
|
returns
+------+-------------+-------+------+---------------+---------+---------+---------------------+------+----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+---------+---------+---------------------+------+----------------------------------------------------+
|
| 1 | SIMPLE | fac | ref | PRIMARY,Data | Data | 4 | const | 1 | Using index condition; Using where; Using filesort |
|
| 1 | SIMPLE | det | ref | Invoice | Invoice | 48 | xxxxxxx.fac.Invoice | 3 | |
|
+------+-------------+-------+------+---------------+---------+---------+---------------------+------+----------------------------------------------------+
|
With function use:
EXPLAIN
|
SELECT
|
xxxx
|
FROM
|
invoices fac
|
INNER JOIN details det ON fac.Invoice = det.Invoice
|
WHERE
|
fac.`Data` = DATEADD_BUSINESSDAYS(CURDATE(),-1)
|
AND fac.Invoice LIKE 'PVD%'
|
AND fac.Route IN ('038','205','211')
|
GROUP BY
|
fac.Client,
|
fac.DeliveryID
|
returns
+------+-------------+-------+-------+---------------+---------+---------+---------------------+--------+----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+---------------------+--------+----------------------------------------------------+
|
| 1 | SIMPLE | fac | range | PRIMARY | PRIMARY | 47 | NULL | 215090 | Using index condition; Using where; Using filesort |
|
| 1 | SIMPLE | det | ref | Invoice | Invoice | 48 | xxxxxxx.fac.Invoice | 3 | Using where |
|
+------+-------------+-------+-------+---------------+---------+---------+---------------------+--------+----------------------------------------------------+
|
Is there any way to optimize this type of queries (with function use in 'where')?
|