Strategy to improve performance on key-value pairs table

MySQLquerysql

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

Best Answer

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.

Related Topic