Newsgroup: it.comp.www.php
Titolo: join or not join... this is the problem

Salve,
posto anche qui (oltre che su it.comp.software.database) un quesito che
riguarda principalmente l'efficienza di query su MySQL ma che credo sia
di interesse anche per questo newsgroup, dato che potrebbe senz'altro
avere delle implicazioni sul diverso approccio da utilizzare in PHP
quando ci si trova a dover interroggare due tabelle MySQL con JOIN.
Analizzando il codice di un noto script open source che utilizza PHP/MySQL
ho notato che in una certa situazione viene evitato l'utilizzo di JOIN SQL.
Supponiamo, semplificando tutto il discorso, di avere due tabelle MySQL
in relazione tra loro, con "user_rank" che è chiave esterna rispetto a
"rank_id":
user
- user_id
- user_name
- user_rank
rank
- rank_id
- rank_desc
Supponiamo ora di dover mostrare l'elenco degli utenti con l'indicazione del
rank cui ognuno di essi appartiene, considerando che nella tabella user
sono presenti numerosi record mentre nella tabella rank sono presenti
poche decine di record.
Quello che mi chiedo è quale approccio SQL sia mediamente più efficiente:
a) Una query con left join tra le tabelle user e rank
b) Due query singole rispettivamente su rank e su user per poi mappare da
PHP user_rank con rank_desc
Soluzione A
SQL: SELECT user_name, rank_desc FROM user LEFT JOIN rank ON
user_rank=rank_id LIMIT ...
Soluzione B
SQL: SELECT rank_id, rank_desc FROM rank
SQL: SELECT user_name, user_rank FROM user LIMIT ...
PHP: Mappatura tra user_rank ed il corrispondente rank_desc
Ovviamente, la valutazione di soluzione più efficiente la intendo in termini
SQL, dunque escludendo qualsiasi ulteriore considerazione riguardante la
mappatura che nel caso B dovrà poi effettuare il PHP.
Attendo impazientemene i vostri pareri.
Grazie,
Riccardo
-- 
* Immolo - Portale immobiliare
* 
http://www.immolo.it

 user_rank=rank_id LIMIT ...
??????
se il tutti gli user hanno un rank(e direi che se cosi' non fosse c'e' 
qualcosa che non va nel db) va usata questa query:
SELECT user_name, rank_desc FROM user INNER JOIN rank ON user_rank=rank_id
il left non avrebbe senso.

 
Ogni scelta va valutata nel suo contesto, ma la soluzione A offre 
maggior lavoro al database, la soluzione B delega un pò di lavoro al 
codice. Per efficienza a cosa ti riferisci? Velocità? Consumo di 
risorse? Data la potenza di calcolo e le prestazioni degli attuali dbms, 
la soluzione B mi sembra un'inutile complicazione; alcune volte non è 
possibile sfruttare funzioni evolute dei db per problemi di 
compatibilità/portabilità, realizzando via codice ciò che si potrebbe 
ottenere in attimo con il dbms (vedi una funzione stupida, come il 
group_concat di mysql).
Il server mysql esegue la query, trasmette i dati al PHP e lui deve 
elaborarli. Se il mysql e il php sono su due diverse macchine abbiamo 
anche il tempo speso per il "doppio" trasferimento dei dati 
effettivamente utilizzati.
Prendendo spunto dalla tua domanda, fino a che punto conviene spostare 
elaborazioni dati sul dbms? Attraverso le store procedure, si riesce a 
fare tantissime cose....
Nel mio piccolo cerco di far lavorare al massimo il dbms e meno il php, 
questo per diversi motivi tra cui "riutilizzare" le query.
	Francesco

 il left non avrebbe senso.
ma anche la mappatura non ha molto senso cioe' gia' per 2 o 3 record io 
non la userei(al max allora si potrebbero usare delle costanti et 
similia a mo di enumeratore e su db un field di tipo intero) ma 
addirittura decine di record. -_-

 ha scritto nel messaggio 
news:fu4kvn$upd$1...@aioe.org...

La tua osservazione è assolutamente corretta, tuttavia il mio è solo un 
esempio...
Comunque, nel caso reale mi ritrovo ad avere degli user senza rank
dunque devo per forza usare la left join e non posso usare inner join.
Non voglio discutere sulla join o left join perchè non è questo il senso 
della mia domanda.
Ciao,
Riccardo
-- 

 ha scritto nel messaggio 
news:fu4lcb$7h1$1...@aioe.org...

Immagina di avere dei rank che non possono essere definiti a priori
perchè magari sono dinamici nel tempo e vengono creati a piacimento.
Comunque, il mio dubbio è sulla soluzione A o B, e non su questo.
-- 

 Francesco
Grazie,
Riccardo
-- 

 Comunque, il mio dubbio è sulla soluzione A o B, e non su questo.
non lo so... fai dei test con un bel tot di dati e printa i tempi di 
elaborazione per ogni soluzione sia A che B, magari poi postali anche 
qua cosi' vediamo anche noi! :-D

 la soluzione B (due query semplici con mappatura PHP) ?
Basta provare....
	Francesco

 
Situazione:
tabella A: 348.924 record
tabella B: 24.692 record
select SQL_NO_CACHE A.autorizzata, B.nominativo from A left join B 
using(chiave) order by A.autorizzata limit 1200,10;
Impiega 1.31 sec (con output del risultato)
Select SQL_NO_CACHE A.autorizzata from A order by A.autorizzata limit 
1300,10 ;
Impiega 1.20 sec (sempre con output)
Togliendo l'order by si ottiene rispettivamente 0.05 contro i 0.02
Ovviamente ho preso due tabelle a caso, il campo chiave è index in A e 
PK in B
Mi sfugge come estrapolare i dati da B: 10 query? Oppure prendi tutta la 
tabella B (24.692 record) e cerchi via PHP i campi "chiave" che 
corrispondano? Fai una select B.nominativo from B where B.chiave 
IN(V1,V2,V3) ???
Comunque la differenza rilevata non spinge la mia curiosità a proseguire...
PS Prova eseguita sui dati a disposizione.
	Francesco

 IN(V1,V2,V3) ???
Pensavo di riempire per ogni tabella secondaria degli array indicizzati sul 
campo chiave.
La successiva mappatura avverrebbe in modo diretto sfruttando l'indice 
dell'array.
Ti illustro il mio caso affinchè possa rendere meglio l'idea.
Ho un sito di annunci immobiliari e vorrei ottimizzare/minimizzare il carico
di lavoro del server MySQL.
Sto lavorando a 360° per ottimizzare struttura del DB e query.
Grosso modo ho una tabella principale di annunci (anche se in verità
la tabella annunci è spezzata in due sotto-tabelle per motivi di efficienza)
che contiene circa 750000 record e delle tabelle secondarie:
reparti: 2 record
gruppi: 2 record
categorie: 36 record
regioni: 20 record
provincie: 107 record
comuni: 8101 record
Le tabelle secondarie sono fondamentalmente tutte costituite da un codice
oppure da un ID e da un nome/descrizione.
Le query eseguite più frequentemente riguardano la selezione di annunci
completi delle informazioni descrittive delle varie sottotabelle.
L'esempio completo e dunque non troppo semplificato della domanda
che mi pongo è... cosa sia meglio fare RIPETUTAMENTE tra:
A) Una query con 6 left join tra le tabelle annunci e le 6 tabelle
secondarie
B) Una query senza left join sugli annunci e 6 query singole sulle tabelle
secondarie per poi mappare i valori descrittivi tra annunci e tabelle
secondarie direttamente da PHP
Tieni presente anche che la query sugli annunci ha dei LIMIT impostati.
P.S.
Mi chiedo anche se ho fatto veramente bene a suddividere la tabella annunci
in due sottotabelle (annunci_base, annunci_text)
per far si che la prima tabella maggiormente utilizzata per selezionare i
record abbia un tipo di record di tipo "fisso" ed una lunghezza
più ridotta per aumentare le prestazioni di ricerca e ordinamento
specialmente nel caso di utilizzo di tabelle temporanee da parte di mysql
per eseguire le query richieste... ma questo è tutto un altro discorso,
magari ne parliamo un'altra volta.
Ciao,


 magari ne parliamo un'altra volta.
Fai conto che i campi text/blob vengono memorizzati su un altro file, 
quindi se la ricerca non tocca tale campo, dovrebbe essere influente.
Un ultima cosa, ma vale la pena spendere tutto questo tempo alla ricerca 
di un decimo di secondo in una query, quando devi scrivere N righe di 
codice per ottenere il solito risultato con nessuna certezza sulle 
prestazioni?
	Francesco

Scusa se intervengo in questa discussione, ma penso che a volte il 
problema debba essere pure visto sotto altri punti di vista....
Personalmente ritengo che queste 6 join non appesantiscano di molto 
l'operato di mysql, anche perché tranne la tabella dei comuni penso che 
le altre sia possibili averle tutte in cache (e magari definirle 
readonly...)
Se il carico di lavoro sul db server è troppo (allora che succede sul 
web server ???) io metterei un altro web server, con replica (che ti da 
ridondanza), e bilancerei il carico fra i due....
Non l'ho mai fatto personalmente ma farei così....
Oppure prenderei un hardware più performante...
Se invece stiamo facendo accademia, andrei a crearmi la stringa di 
query, farei vari test con DESCRIBE per vedere se mysql interpreta bene 
la richiesta, proverei a spostare l'ordine dei campi nella WHERE... 
insomma un po di prove per cercare di risparmiare qualche ciclo di clock.
L'altra strada è quello poi di creare l'array come dicevi, ma non 
leggendo dal db, ma codificando i valori nel programma PHP.... regioni, 
province, comuni non è che cambiano tutti i giorni !!!!! E a quel punto 
anche reparti, gruppi....
della serie
$regione[1] = "Lazio"
Francesco

 Francesco
Grazie mille,


 efficiente.
Quando hai fatto le prove (che diano risultati consistenti) mi posti i 
risultati ? E magari in privato la query se ti va...
Ad occhio penso le due soluzioni come tempo di cpu totale (web server, 
db server, etc) siano paragonabili... potresti poi fare delle 
ottimizzazioni a livello di creazione della query....
ad esempio se hai il filtro su un comune, avrai che provincia e regione 
sono fisse e così puoi mettere:
SELECT ...."Lazio","Roma","Roma",..... WHERE comune="001"
senza mettere la join...
Andrebbe considerato il "tipico caso d'uso"... è obbligatorio 
specificare una regione ? allora potrei provare a splittare il db in 20 
database diversi così da poter in poco tempo spostare alcune regioni su 
altro db....
In conclusione, per cercare di ottimizzare occorre avere chiari tutta 
una serie di parametri... Nel tuo caso, penso che la query sia molto 
semplice e lineare, quindi ritengo che i margini di miglioramento non 
siano apprezzabili.... penso....
Francesco
PS: lo scorso weekend un mio amico che lavora con Oracle mi ha detto di 
aver modificato una query portando il tempo di risposta da 7 MINUTI a 3 
SECONDI..... non male.... ma era una query composta da 197 
(centonovantasette) righe...