Re: Server times out when GROUP BY has more than one group

[Mike Mohr]

Thank you,Peter.
Yes, f_avg_salary() is a UDF compiled into my server. Here is its code:

DELIMITER $$
CREATE FUNCTION f_avg_salary (p_dept CHAR(4), p_gender enum('M','F'),
p_min INT, p_max INT) RETURNS
DECIMAL(10,2)
DETERMINISTIC NO SQL READS SQL DATA
BEGIN
DECLARE v_avg_salary DECIMAL(10, 2);
SELECT avg(s.salary)
INTO v_avg_salary
FROM t_salaries s
JOIN t_employees e ON e.emp_no = s.emp_no
JOIN t_dept_emp de ON e.emp_no = de.emp_no
WHERE e.gender = p_gender
AND de.dept_no = p_dept
AND s.to_date >= CURDATE()
AND s.salary BETWEEN p_min AND p_max;
RETURN v_avg_salary;
END $$
DELIMITER ;

EXPLAIN SELECT results:
id, 1
select_type, SIMPLE
table, de
partitions, NULL
type, index
possible_keys, PRIMARY
key, PRIMARY
key_len, 20
ref, NULL
rows, 331596
filtered, 100.00
Extra Using index; Using temporary;
Using filesort