I am using MySQL 5.6.25 with entire data set in memory.
Have already created index on (name,value).
Query explain plan ;
+----+-------------+-----------------+-------+---------------+-------------+---------+-------------+--------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+-------+---------------+-------------+---------+-------------+--------+----------+--------------------------+
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 93752 | 100.00 | NULL |
| 1 | PRIMARY | <derived3> | ref | <auto_key0> | <auto_key0> | 8 | c.user_id | 10 | 100.00 | NULL |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 8 | c.user_id | 10 | 100.00 | NULL |
| 4 | DERIVED | attribute_value | ref | IDX_kk | IDX_kk | 1535 | const,const | 93752 | 100.00 | Using where; Using index |
| 3 | DERIVED | attribute_value | range | IDX_kk | IDX_kk | 1535 | NULL | 901543 | 100.00 | Using where; Using index |
| 2 | DERIVED | attribute_value | ref | IDX_kk | IDX_kk | 1535 | const,const | 120682 | 100.00 | Using where; Using index |
+----+-------------+-----------------+-------+---------------+-------------+---------+-------------+--------+----------+--------------------------+