The situation
-------------

Our system has a `product` table whose parameters can't be modified. 

In order to add optional extra parameters, a developer designed a key-value pair table which looks a bit like this:

 +----+--------------------+----------+------------+
 | Id | name | value | product_id |
 +----+--------------------+----------+------------+
 | 1 | provider_id | 3 | 2 |
 | 2 | appliable_discount | 20% | 2 |
 | 3 | pay_mode | pre-pay | 2 |
 | 4 | pay_mode | post-pay | 3 |
 | 5 | appliable_discount | 15% | 3 |
 | 6 | provider_id | 4 | 4 |
 | 7 | provider_id | 3 | 5 |
 | 8 | expires | 1 | 2 |
 | 9 | expires | 0 | 4 |
 | 10 | color | red | 2 |
 | 11 | color | blue | 4 |
 | 12 | foo | 1 | 2 |
 +----+--------------------+----------+------------+

Most of this parameters never change.

Problem
-------

In one part of our application there is a query to extract all products and a subset of the extra parameters that look like this:

<!-- language: sql -->

 SELECT p.*, sp.value AS hours, cp.value AS appliable_discount, cp2.value AS pay_mode, cp3.value AS provider_id, cp4.value AS expires, cp5.value AS foo, cp6.value AS bar, cp7.value AS etc
 FROM products AS p
 LEFT JOIN product_extra_parameters AS cp ON cp.product_id = p.id AND cp.name = 'appliable_discount'
 LEFT JOIN product_extra_parameters AS cp2 ON cp2.product_id = p.id AND cp2.name = 'pay_mode'
 LEFT JOIN product_extra_parameters AS cp3 ON cp3.product_id = p.id AND cp3.name = 'provider_id'
 LEFT JOIN product_extra_parameters AS cp4 ON cp4.product_id = p.id AND cp4.name = 'expires'
 LEFT JOIN product_extra_parameters AS cp5 ON cp5.product_id = p.id AND cp5.name = 'foo'
 LEFT JOIN product_extra_parameters AS cp6 ON cp6.product_id = p.id AND cp6.name = 'bar'
 LEFT JOIN product_extra_parameters AS cp6 ON cp7.product_id = p.id AND cp7.name = 'etc'
 WHERE p.id > 1

As you may imagine, performance of this query is really low and we are trying to improve it. We suspect the abuse of left join is the main issue.

*Note: To the effect of this question, ignore the fact that it's fetching all products.*

What I am looking for
---------------------

**Short term:** If it exists, a new strategy to query this table, to get the same information with better performance.

**Long term:** A better strategy to store this information in a relational database, or to cache that information for better performance. 

Since 24 hours old information is acceptable, a solution on the lines of "a cron that updates a table with key: <product_id>, name: <json with all extra_parameters>" would be acceptable.

**An answer doesn't have to provide both a short term and a long term solution to be accepted.**