[mysql] migliorare query con indici e chiavi esterne

[faduns]

ciao a tutti..
allora, ho 2 tabelle entrambe con chiave primaria univoca auto_increment

tabella 1: ingredienti

id | periodo | ricetta | ingrediente


tabella 2: prodotti

id | periodo | ricetta | prodotto


devo estrarre una tabbella fatta cosi:

periodo | ingrediente | prodotto

si tratta di tabelle con milioni di righe, come posso migliorare l'efficienza della query magari con indici e chiavi esterne?

la query base è questa:

SELECT ingredienti.periodo, ingrediente, prodotto, FROM prodotti INNER JOIN ingredienti ON prodotti.ricetta = ingredienti.ricetta;

al momento nn ho definito nessun indice e nessuna chiave tranne quella primaria id per entrambe le tabelle..

chi ha qualche idea..??
grazie a tutti...

[frinkia]

Cavoli, quante ricette che hai

Di solito la strada e' creare un indice per il campo su cui effettui la ricerca. Attenzione pero' a crearne piu' di uno perche' i tempi di inserimento aumentano all'aumentare del numero di indici). Per evitare la scansione sequenziale nella query sotto, ovviamente devi mettere un indice sui due campi "ricetta". Peraltro, dubito che quella sotto sia l'unica query che effettui sulle due tabelle e i problemi di ottimizzazione degli indici si fanno con carta, penna e calcolatrice.

Se magari mi dai lo schema del database (con uova, farina e tutto), posso dirti se e' normalizzato correttamente e quali migliorie si possono eventualmente fare.

[faduns]

in realtà ho semplificato i nomi dei campi... si tratta di milioni di righe poichè sono dati provenienti da un modello di simulazione ad agenti che nulla a che a vedere con uova e farina..

allora:
nn ho problemi di inserimento nuovi record..
per ora mi interessa solo la query descritta prima!
in realtà prima ho omesso l'istruzione DISTINCT:

SELECT distinct ingredienti.periodo, ingrediente, prodotto, FROM prodotti INNER JOIN ingredienti ON prodotti.ricetta = ingredienti.ricetta;

quindi:
indice su ricetta su entrambe le tabelle
indice su ingredienti e indice su prodotti??

ma mysql nn utilizza solo un indice per tabella??

[skidx]

Originariamente inviato da faduns
ma mysql nn utilizza solo un indice per tabella??

No, ne puoi mettere tanti (32 mi sembra, ma non ricordo il numero esatto).

In ogni modo il campo ricetta è una chiave numerica esterna, giusto?
Se non lo è, fai in modo che lo sia, prima di creare i corrispettivi indici.

[piero.mac]

Originariamente inviato da skidx
No, ne puoi mettere tanti (32 mi sembra, ma non ricordo il numero esatto).

In ogni modo il campo ricetta è una chiave numerica esterna, giusto?
Se non lo è, fai in modo che lo sia, prima di creare i corrispettivi indici.

forse anche 64. Ma come hai annotato tu giustamente prima dovrebbero essere ottimizzate le tabelle.

prodotti.periodo e' un dato inutilmente duplicato. il campo prodotti.ricetta dovrebbe essere riferito a ingredienti.id e non a ingredienti.ricetta

E forse sarebbe pure opportuno fare una tabella "periodi" per evitare valori replicati in tab "ingredienti"

[frinkia]

Ci mancherebbe che usasse un indice per tabella - Non confondere indici e chiavi primarie eh

[faduns]

allora forse se spiego meglio le tabella che ho potete capirci di più:
composizione di una ricetta: ric1=ingred1+ingred2=prod1

tabella 1: ingredienti

id | periodo | ricetta | ingrediente
1 1 1 1
2 1 1 2
3 1 2 3
4 1 2 4
5 2 1 1
6 2 1 2
7 2 2 3
8 2 2 4
9 2 3 5
10 2 3 2
11 3 1 1
11 3 1 2
12 3 2 3
13 3 2 4
14 3 3 5
15 3 3 2
16 3 4 6
17 3 4 7

e cosi via.. ogni ricetta è composta da min 2 ingredienti e max 3. In ogni periodo si ripetono le stesse ricette e se ne fa qualcuna in più usando nuovi o vecchi ingredienti.

tabella 2: prodotti

id | periodo | ricetta | prodotto
1 1 1 1
2 1 2 2
3 2 1 1
4 2 2 2
5 2 3 3
6 3 1 1
7 3 2 2
8 3 8 3
9 3 4 4

es: nel periodo 1 sono state prodotte la ricetta 1 e la 2, che hanno prodotto 1 e 2.. e cosi via..
a me non serve sapere quante ricette sono state prodotte in ogni periodo, ma voglio un elenco degli ingredienti e dei prodotti per perido.
il risultato deve essere:

periodo | ingrediente | prodotto
1 1 1
1 2 1
1 3 2
1 4 2
2 1 1
2 2 1
2 3 2
2 4 2
2 5 3
2 2 3
3...
3....

il campo prodotti.ricetta può essere una chiave esterna per ingredienti.id? penso di no visto la logica delle tabelle!!
allora cosa mi consigliate di fare visto che ora dovrebbe essere tutto più chiaro??