una query da 6 secondi, come ottimizzare?

[james]

Per semplificare il mio problema, supponiamo di avere due tabelle, una prodotti, ed una foto.

Ogni prodotto può avere delle foto, può non averne affatto e se ne ha, può averne quante se ne vuole, quindi ho organizzato la tabella foto così

foto_id
foto_id_prodotto
foto_campo... blob che contiene la foto

ora vorrei tirare fuori li lista dei prodotti, e visualizzare accanto al prodotto una delle foto ad esso relative se ne ha, una sola, a caso. Per far questo avevo scritto questo query

SELECT prodotto_id, foto_id
FROM prodotti LEFT JOIN foto ON prodotto_id=foto_id_prodotto;

Solo che in questa maniera mi duplica i record, nel senso che visualizza tutti i prodotti che hanno più foto più volte, tante quante le foto che ci sono, e le tira fuori ognuna con la foto diversa.

Il problema non è risolvibile ne con la Inner join ne con la right join, la soluzione deve essere un'altra.

Ho provato con DISTINCT prodotto_id, ma con la LEFT JOIN non ha nessun effetto, quindi ho trovato un'altra soluzione, una query di questo tipo:

SELECT prodotto_id, prodotto_id AS contatore, foto_id
FROM prodotti LEFT JOIN foto ON prodotto_id=foto_id_prodotto
GROUP BY prodotto_id;

la cosa funziona, ma il problema è una altro, mentre la prima query viene eseguita in un lampo, la seconda impiega circa 6 secondi, perchè la query ve l'ho postata un po' semplificata, altrimenti ci sarebbero altre 6 JOIN, nella clausola HAVING ci sono circa 4 condizioni e la tabella prodotti ha circa 30.000 record.

Come posso trovare una soluzione che impieghi un tempo ragionevole per tirare fuori questi dati in questa maniera??

[andr3a]

credo sia importante vedere tutta la query o si rischia di sparare a caso

[daniele_dll]

Beh, apparte che mi pare assurdo 6 secondi per una query, quanta roba tira fuori???

Dopo di che oltre a postare il codice dell'sql posta anche il tuo codice in toto perché probabilmente ci colpa pure

Comunque, spero che il db sia normalizzato correttamente, altrimenti anche li va tutto lento

Innanzi tutto io cambierei sistema, ovvero, fare 2 query:
- la prima mi estrae i prodotti da visualizzare mettendoli in un'array e costruendo una stringa che contiene un'elenco di ID separati da virgola
- la seconda estrae le foto usando quell'id (la clausola IN nel WHERE)

A questo punto con la seconda query inserisci l'elenco delle foto nell'elemento dell'array corrispondente al prodotto

dopo di che cicli tutto e lo stampi a video

[james]

Nel codice non c'è quasi nulla, l'ho semplificato alla sola query e visualizzazione dati su di un while.

Il sistema delle due query mi comporta non poche complicazioni, ad esempio, quando scorro l'array con i dati per la visualizzazione, devo visualizzare la foto corrispondente, come gestisco questa "sincronizzazione" tra gli indici degli array?

[james]

tra l'altro se la stessa query la eseguo da phpmyadmin, sempre 6 secondi ci impiego, non è il codice, è la query che è complessa.

Tabella prodotti 32.517 record
Tabella foto 3 record
Altra Tabella 1 8069 record
Altra Tabella 2 107 record
Altra Tabella 3 3 record
Altra Tabella 4 8 record
Altra Tabella 5 20 record

La query interseca questi dati, il risultato è di 3.457 record

[james]

probabilmente le operazioni di raggruppamento su un numero di record così elevato si fanno sentire, secondo me il problema è questo

[andr3a]

41.000 records non sono così eccessivi, probabilmente usi una versione datata dell'engine o durante l'intersezione duplichi o quadruplichi qualche risultato .... oppure ... beh, come ho detto è un pò difficile aiutarti se non vediamo bene tutta la query poichè non abbiamo elementi a sufficienza per valutare quale possa essere il collo di bottiglia.

I tuoi primi due esempi, raramente impiegheranno 6 secondi, per farti un esempio di quanto sto dicendo

[daniele_dll]

a me sembrano pochissimi, cioe ... non è che sono chissa che cosa, anzi

inoltre considera un'importante cosa: tu non li estrai TUTTI d'un colpo bensi solo una piccola porzione che è quella che visualizzi

e comunque il lavoro che andresti a fare è molto banale:

1- Esegui query di estrazione dati
2- While sui dati che crea un'array formato cosi $prodotti[ID-PRODOTTO] = RIGA-FETCHATA, inoltre costruisci una stringa con gl
3- Costruisci l'elenco degli id usando un bel join(', ', array_keys($prodotti))
4- Esegui la query sulla tabella delle immagini usando WHERE id-foto IN ('$STRINGA-ELENCO-ID')
5- Cicli l'array con il while aggiungendo all'array prodotti un campo col nome foto che contegga elementi con la chiave corrispondente all'id della foto, quindi $prodotti[ID-PRODOTTO]['foto'][ID-FOTO] = RIGA-FETCHATA
6 per fare la visualizzazione cicli l'array $prodotti facendo while(list($id_prodotto, $informazioni_prodotto) = each($prodotti)) { ........ } e fai la normale visualizzazione. Dopo di che se ti serve visualizzare tutte le foto le cicli cosi altrimenti usi un each singolo sull'elemento e fai la normale visualizzazione

Ma anche con la normale left/inner join ti basta prima costruire l'array e poi visualizzarlo semplicemente quando costruisci l'array verifici se c'è già l'elemento e se si non lo sovrascrivi aggiungi semplicemente le informazioni alla chiave foto, tipo

if (isset($prodotti[ID-PRODOTTO]) === false)
{
$prodotti[ID-PRODOTTO] = $informazioni_prodotto
}

$prodotti[ID-PRODOTTO]['foto'][ID-FOTO] = $informazioni_foto

PS: non vorrei fosse un problema legato all'harware, alla poca memoria di mysql, ad un'errata configuarzione del sistema o del db ... perché potrebberò anche causare questi problemi! Usi qualcosa di pre-pronto come easyphp, xamp o cose simili?

[Graboid]

Puo' per caso dipendere anche dal server?

[james]

molte delle condizioni di selezione le avevo messe dentro la clausola having anzichè WHERE, togliendo tutto dall'HAVING e portando tutto sul WHERE ho guadagnato ben 4 secondi, da 6 sono passato a 2, il che mi potrebbe anche stare bene