mysql – Dynamic pivot table filtering and performance

I have this those tables and i’m trying to rotate the subscriber to column table to horzental and filter it’s result based on multiple AND/OR conditions like following

    WHERE  first_name LIKE 'm%' AND email LIKE '%com'

This is the fiddle
http://sqlfiddle.com/#!9/a7211d/1

Those are my 2 tables

Fields Table
+----+------------+
| id |label       |
+----+------------+
|  1 | email      |
|  2 | first_name |
|  3 | last_name  |
+-----------------+

Subscribers Fields Table
+----+--------------+----------+---------------+-------------------+
| id | mail_list_id | field_id | subscriber_id | value             |
+----+--------------+----------+---------------+-------------------+
|  1 |            1 |        1 |             1 | mark@examble.com  |
|  2 |            1 |        2 |             1 | Mark              |
|  3 |            1 |        3 |             1 | Wood              |
|  4 |            1 |        1 |             2 | luan@domain.com   |
|  3 |            1 |        2 |             2 | Luan              |
|  4 |            1 |        3 |             2 | Charles           |
|  5 |            1 |        1 |             3 | marry@domain.com  |
|  6 |            1 |        2 |             3 | Anna              |
|  7 |            1 |        3 |             3 | Marry             |
|  8 |            2 |        1 |             4 | kevin@domain.com  |
|  9 |            2 |        2 |             4 | Kevin             |
| 10 |            2 |        3 |             4 | Faustino          |
| 11 |            2 |        1 |             5 | frank@examble.com |
| 12 |            2 |        2 |             5 | Frank             |
| 13 |            2 |        3 |             5 | Denis             |
| 14 |            2 |        1 |             6 | max@example.com   |
| 15 |            2 |        2 |             6 | Max               |
| 16 |            2 |        3 |             6 | Ryan              |
+----+--------------+----------+---------------+-------------------+

This is what i tried but that caused to issues that the email and first_name return 0 instead of value and also it dont work with AND condition operator

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
WHERE (field_id = 3 AND value LIKE 'm%') OR (field_id = 1 AND value = '%com')
group by subscriber_id limit 100;

However if i removed the Where condition the query works fine with good performance

I also tried to add my query in a subquery give it an alias and then search that generated virtual table using the alias field name instead of the field id but in this case i will have to remove the limit parameter from the subquery in order to be able to search for the full table not just in the first 100 records which cause a very bad performance since this table will be too large 100-500 milion record and i need to get the query result in under 4 seconds.