CODEWITHSUNDEEP
mysqldatabase-designdatabase-optimization
Scott Moniz
Scott Moniz

Reputation: 660

MySQL Optimization for Queries with Functions in Where CLause

So let's assume I have an employee table (employee) and I have thousands of records in there. I need to limit the employees you can see based on your login for re-usability I create a function fn_HasEmployeeAccess(t_userid int, t_employee_id INT) that will return true/false to determine whether you have access to the employee or not.

This function looks something like this (although it does other checks, for example, you cannot access your own profile, you cannot access other employees that share a position with you etc):

 FUNCTION fn_HasEmployeeAccess(t_userid INT, t_employee_id INT)
             DECLARE isAdmin BOOL;
             DECLARE isDirector BOOL;
             DECLARE empStatus INT;

             SELECT admin, director INTO isAdmin, isDirector FROM users 
             WHERE users.userid=t_userid;

             /*select the status of this user if it is in your dept
             if employee is not in your department - status will be null 
             which will fail the checks below and return 0*/
             SELECT employee.status INTO empStatus FROM employee 
             INNER JOIN users ON employee.department = users.department
            WHERE employee.employeeID=t_employee_id;

             IF empStatus IN (1,2) AND isAdmin THEN
                     RETURN 1;
             ELSE IF empStatus=2 AND userIsDirector THEN
                     RETURN 1;
            ELSE
                     return 0;
            END IF;
    END FUNCTION

Now all of this works and is great, except when you have a lot of data and need to query using multiple joins and then on top of that filter based on the result of this function - the performance degrades quite a bit and I believe I understand - the function is being run for every row in the employee table.

For example:

SELECT * FROM employee e
INNER JOIN employee_attachments a ON e.employeeID = a.employeeID
WHERE fn_HasEmployeeAccess(12345, e.employeeID)=1

How can I prevent what I would assume is full table scans due to the function usage? Are their alternatives to this pattern?

The function is used for filtering almost everywhere in the application (dashboards, views, reporting) etc and if I wanted to adjust access to an employee, I currently just have to adjust the function. It has been suggested I just inline all of this logic but everytime we add a status, or want to change access logic this would entail wading through the system to change every query, every report, every section pertaining to an employee.

Also - I have heard functions can take advantage of indexes but only if the functions are deterministic. Would a function like this be deterministic? Given the same employeeID and userID it should return the same result....unless the user changes from an admin to director...then the function would return a different result. Is this function deterministic or non deterministic?

Any insight is appreciated

Upvotes: 0

Views: 121

Answers (2)

Rick James
Rick James

Reputation: 142278

In a newer version of MySQL/MariaDB, you can create a Virtual (Computed) column and index it. The new column would be populated via your function. With that, don't call the function in the WHERE clause, simply use the new column.

If (when) you add a new status, do a suitable ALTER TABLE to rebuild the column and index.

Upvotes: 2

Brian
Brian

Reputation: 1248

If I were facing this, I would calculate the result of the "Employee Has Access" call and store it (possibly encoded, for security) in the table directly, via an overnight process. Then I'd attach a trigger to the fields that affect the results of "Employee Has Access" so that if the values get updated, the result gets recalculated.

Upvotes: 1

Related Questions