Lazily evaluate MySQL view

Question

I have some MySQL views which define a number of extra columns based on some relatively straightforward subqueries. The database is also multi-tenanted so each row has a company ID against it.

The problem I have is my views are evaluated for every row before being filtered by the company ID, giving huge performance issues. Is there any way to lazily evaluate the view so the 'where' clause in the outer query applies to the subqueries in the view. Or is there something similar to views that I can use to add the extra fields. I want to calculate them in SQL so the calculated fields can be used for filtering/searching/sorting/pagination.

I've taken a look at the MySQL docs that explain the algorithms available and am aware that the views can't be proccessed as a 'merge' since they contain subqueries.

view

create view companies_view as
select *,
(
    select count(id) from company_user where company_user.company_id = companies.id
) as user_count,
(
    select count(company_user.user_id)
    from company_user join users on company_user.user_id = users.id
    where company_user.company_id = companies.id
        and users.active = 1
) as active_user_count,
(
    select count(company_user.user_id)
    from company_user join users on company_user.user_id = users.id
    where company_user.company_id = companies.id
        and users.active = 0
as inactive_user_count
from companies;

query

select * from companies_view where company_id = 123;

I want the subqueries in the view to be evaluated AFTER applying the 'where company_id = 123' from the main query scope. I can't hard code the company ID in the view since I want the view to be usable for any company ID.

Answer 1

You cannot change the order of evaluation, that is set by the MySQL server.

However, in this particular case you could rewrite the whole sql statement to use joins and conditional counts instead of subqueries:

select c.*,
       count(u.id) as user_count,
       count(if(u.active=1, 1, null)) as active_user_count,
       count(if(u.active=0, 1, null)) as inactive_user_count 
from companies c
left join company_user cu on c.id=cu.company_id
left join users u on cu.user_id = u.id
group by c.company_id, ...

If you have MySQL v5.7, then you may not need to add any further fields to the group by clause since the other fields in the companies table would be functionally dependent on the company_id. In earlier versions you may have to list all fields in the companies table (depends on the sql mode settings).

Another way to optimalise such query would be using denormalisation. Your users and company_user table probably have a lot more records than your companies table. You could add a user_count, an active_user_count, and an inactive_user_count field to the companies table, add after insert / update / delete triggers to the company_user table and an after update to the users table and update these 2 fields there. This way you would not need to do the joins and the conditional counts in the view.

Answer 2

It is possible to convince the optimizer to handle a view with scalar subqueries using the MERGE algorithm... you just have to beat the optimizer at its own game.

This will seem quite unorthodox to some, but it is a pattern I use with success in cases where this is needed.

Create a stored function to encapsulate each subquery, then reference the stored function in the view. The optimizer remains blissfully unaware that the functions will invoke the subqueries.

CREATE FUNCTION user_count (_cid INT) RETURNS INT
DETERMINISTIC
READS SQL DATA
RETURN (SELECT count(id) FROM company_user WHERE company_user.company_id = _cid);

Note that a stored function with a single statement does not need BEGIN/END or a change of DELIMITER.

Then in the view, replace the subquery with:

user_count(id) AS user_count,

And repeat the process for each subquery.

The optimizer will then process the view as a MERGE view, select the one appropriate row from the companies table based on the outer WHERE, invoke the functions, and... problem solved.