Strategy to improve performance on key-value pairs table

Question

The situation

Our system has a MySQL database with 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 |        key         |  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 |
+----+--------------------+----------+------------+

• id: auto-incremental id.
• key: the name of the key (string).
• value: the value for that key (string).
• product_id: Implicit Foreign Key to the Product.

[key, product_id] pairs are unique. There can't be 2 different values for the same key for the same product.

Changes on the value columns are rare. Once set, they usually stay the same.

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:

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.key = 'appliable_discount'
  LEFT JOIN product_extra_parameters AS cp2 ON cp2.product_id = p.id AND cp2.key = 'pay_mode'
  LEFT JOIN product_extra_parameters AS cp3 ON cp3.product_id = p.id AND cp3.key = 'provider_id'
  LEFT JOIN product_extra_parameters AS cp4 ON cp4.product_id = p.id AND cp4.key = 'expires'
  LEFT JOIN product_extra_parameters AS cp5 ON cp5.product_id = p.id AND cp5.key = 'foo'
  LEFT JOIN product_extra_parameters AS cp6 ON cp6.product_id = p.id AND cp6.key = 'bar'
  LEFT JOIN product_extra_parameters AS cp6 ON cp7.product_id = p.id AND cp7.key = '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.

---

EDIT: For the long term solution, I've found some valuable insight in this stackoverflow question: https://stackoverflow.com/questions/2909448/best-pattern-for-storing-product-attributes-in-sql-server

Answer 1

How about just getting all parameters and do any filtering logic on the application side. It would cut joins to a single one in the given query, but you'll have to handle the name-value assignment in the application instead of the query (this is an upside actually, it's faster and easier).

The attribute table idea is pretty worn out (but that doesn't mean every generation won't reinvent it), but implementing logic based on those in the database queries is just a really really bad idea.

Answer 2

This is a relatively a common problem with relational db's. It gets annoying adding columns for each parameter/value.

One solution is to use a Pivot Query. Unfortunatley pivot isn't possible in MySQL. But you can use a case statement.

Another option is to hold the key value pairs in XML or JSON. Most db's can index and query these. reasonably efficiently. However other users may find this dificult to work with if they need to query/report using these values.

Answer 3

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.

What indexing do you have on the "product_extra_parameters" table?
The proliferation of joins, left or otherwise, should not be a major issue unless this table is significant in size.

Answer 4

You could horizontally partition your product_extra_parameters table. i.e. product id 1 in one table, product id 2 in another (obviously some sort of rule is needed, not just arbitrary). This can be handled by either the DBMS or even triggers on the relevant core table.

A quick google search found the following regarding horizontal partitioning on MySQL: https://dev.mysql.com/doc/refman/8.0/en/partitioning-overview.html