MySql view with group_by very slow

I have two tables, appointments and clients. The tables are big, each with about 100M records.

• each has a primary key on id
• clients has a foreign key on appointment_id

I’m using mysql 5.7

As one would expect, the following query is lightning fast:

select a.id, count(c.id) client_count
from appointments a
left join clients c on c.appointment_id = a.id
where a.id = 499
group by a.id;

But, if I create the following view and query, it is interminably slow:

create view client_counts as
select a.id, count(c.id) client_count
from appointments a
left join clients c on c.appointment_id = a.id
group by a.id;

select id, client_count
from client_counts
where id = 499;

I’m assuming the where clause is not being applied to the inner query (in the view), but rather every record of the view must be scanned to see if 499 is a match. I think this is caused by the group by.

Note: I did see that I can use a function in the where clause of the view, but it seems very clunky. Is this the preferred method?

My Question: What’s the best way I can continue to use a view and have it be fast?

Answer

I think the best approach is to use a function; if you were using Postgres, maybe using a materialized view could solve your problem if the records don’t change much… but in general I think you should use a function here, or just do a query instead of using a view.

Anyway, it’s a good idea to check if the id fields are indexed.