Dynamic pivot table filtering and performance
You can use HAVING to filter the columns you created:
select 
  subscriber_id,
  MAX(case when field_id = '1' then value else 0 end) as email,
  MAX(case when field_id = '2' then value else 0 end) as first_name,
  MAX(case when field_id = '3' then value else 0 end) as last_name
from test_fields_table

group by subscriber_id
HAVING email LIKE '%com' 
AND last_name LIKE 'M%'
limit 100;

See result