La situazione
Il nostro sistema ha un database MySQL con una tabella product i cui parametri non possono essere modificati.
Per aggiungere parametri extra facoltativi, uno sviluppatore ha progettato una tabella di coppie valore-chiave che assomiglia un po 'a questo:
+----+--------------------+----------+------------+
| 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 : ID auto incrementale
- chiave : il nome della chiave (stringa).
- valore : il valore per quella chiave (stringa).
- product_id : chiave esterna implicita per il prodotto.
[chiave, product_id] sono uniche. Non ci possono essere 2 valori diversi per la stessa chiave per lo stesso prodotto.
Le modifiche sulle colonne value sono rare. Una volta impostati, di solito rimangono gli stessi.
Problema
In una parte della nostra applicazione c'è una query per estrarre tutti i prodotti e un sottoinsieme dei parametri aggiuntivi che assomigliano a questo:
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
Come puoi immaginare, il rendimento di questa query è davvero basso e stiamo cercando di migliorarlo. Sospettiamo che l'abuso di join sinistro sia il problema principale.
Nota: per gli effetti di questa domanda, ignora il fatto che sta recuperando tutti i prodotti.
Cosa sto cercando
Breve termine: se esiste, una nuova strategia per interrogare questa tabella, per ottenere le stesse informazioni con prestazioni migliori.
A lungo termine: una strategia migliore per archiviare queste informazioni in un database relazionale o per memorizzare tali informazioni per prestazioni migliori.
Poiché le informazioni vecchie di 24 ore sono accettabili, una soluzione sulle linee di "un cron che aggiorna una tabella con la chiave: [product_id], nome: [json with all_parametri_extra]" sarebbe accettabile.
Una risposta non deve fornire una soluzione sia a breve che a lungo termine per essere accettata.
EDIT: per la soluzione a lungo termine, ho trovato alcune preziose informazioni in questa domanda di stackoverflow: link