[SQL] Query onerosa

[Manugal]

Ciao a tutti!

Ho bisogno di eseguire una query che, date la tabella

Utenti(id,nickname,sesso,citta,paese)
Libri(idu,titolo,autore) (dove idu è l'id utente che ha quel libro)

mi tiri fuori tutte le coppie di utenti che hanno libri in comune. Ad esempio, se la tabella utenti è fatta così:

id        nickname          ecc. (tutti gli altri campi) 
0          utente0
1          utente1
2          utente2

e quella libri è fatta così:

idu        titolo               autore 
0           libro1             autore1
0           libro2             autore2
0           libro3             autore3
0           libro4             autore4
1           libro2             autore2
1           libro6             autore6
2           libro1             autore1
2           libro3             autore3
2           libro6             autore6

Vorrei che mi tirasse fuori questo:

id1      nickname1    id2      nickname2      NumLibriComune
0         utente0       1         utente1               1
0         utente0       2         utente2               2
1         utente1       2         utente2               1

Le due tabelle contengono migliaia di record. Infatti quando provavo a fare delle query il consumo di memoria saliva alle stelle, arrivando anche a più di 1GB. Già facendo una query simile (per prendere tutte le possibili coppie di utenti):

select u1.id, u1.nickname, u2.id, u2.nickname
from utenti u1, utenti u2
where u1.id<>u2.id

il consumo di memoria (visto da Task Manager) è arrivato a 400MB.

Una cosa del genere in SQL è possibile farla? Posso anche evitare di segnarmi l'attributo NumLibriComune, però in quel caso ci devono essere tante coppie quanti sono i libri in comune.

Spero di essere stato chiaro.

[gugoXX]

Innanzitutto vedo un problema.
Ovviamente la chiave primaria di Libri non e' solo idu, altrimenti significherebbe che ciascun utente puo' avere un solo libro.
Oppure idu non e' la chiave dell'utente, ma solo quella del libro, e da qualche altra parte esiste un'associativa che lega insieme libri e utenti (che sarebbe anche la cosa migliore)

[Manugal]

In realtà mi basterebbero anche solo gli id degli utenti, quindi posso usare anche solo la tabella libri. In tal caso ho provato a eseguire la seguente query:

select l1.idu, l2.idu 
from libri l1, libri l2
where l1.idu<>l2.idu and l1.titolo like l2.titolo

Il problema è che tale query non termina!

[Manugal]

Allora infatti ho sbagliato a scrivere idu non è chiave primaria (Libri in realtà non ha una chiave primaria). Inoltre non c'è alcuna tabella associativa che lega libri e utenti. Purtroppo questo database non l'ho fatto io.

[gugoXX]

Originariamente inviato da Manugal

Allora infatti ho sbagliato a scrivere idu non è chiave primaria (Libri in realtà non ha una chiave primaria). Inoltre non c'è alcuna tabella associativa che lega libri e utenti. Purtroppo questo database non l'ho fatto io.

Ok. non ha chiave primaria.
E allora vediamo se possiamo mettercela.
Data una coppia (idu, titolo), quanti record possiamo trovare nella tabella libri?
1 o piu' di 1?

[Manugal]

Data una coppia (idu,titolo) ne possiamo trovare solo 1. Quindi dici che quella può essere una chiave primaria?

[gugoXX]

Originariamente inviato da Manugal

Data una coppia (idu,titolo) ne possiamo trovare solo 1. Quindi dici che quella può essere una chiave primaria?

Esatto. Ti propongo quindi di aggiungere una chiave primaria
(Idu, titolo) sulla tabella Libro

Di aggiungere anche un indice unico (titolo,IDU) (sempre se te lo fa fare, dopo aver fatto gia' quella chiave primaria)

e poi di eseguire la

SELECT L1.ID,L2.ID,COUNT(*)
FROM Libri L1, Libri L2
WHERE L2.ID>L1.ID
    AND L1.titolo = L2.titolo
GROUP BY L1.ID, L2.ID

[Manugal]

Ho provato ad aggiungere la chiave primaria (idu,titolo) ma mi dice che non è unique

Update: ho scoperto perché. Ci sono alcune righe duplicate (come era ovvio che fosse).

[gugoXX]

Originariamente inviato da Manugal

Ho provato ad aggiungere la chiave primaria (idu,titolo) ma mi dice che non è unique

Update: ho scoperto perché. Ci sono alcune righe duplicate (come era ovvio che fosse).

Allora, prima di tutto, devi chiederti cosa vorresti che venisse risposto a fronte di quei casi li'.

Prova comunque ad aggiungere i 2 indici non univoci, ed eseguire la query di prima. La quale conterebbe pero' 6 un libro letto 2 volte dal primo utente e 3 volte dal secondo utente.

[Manugal]

Allora ho scoperto che non è possibile che (idu,titolo) sia una chiave primaria, perché a quanto pare ci sono libri con lo stesso titolo posseduti dallo stesso utente ma scritti da autori diversi. Un'altra soluzione invece di rendere (idu,titolo) chiave primaria?

[Manugal]

Ho provato ad aggiungere i due indici (uno per idu e uno per titolo) e ad eseguire quella query, ma non termina mai.

[gugoXX]

Originariamente inviato da Manugal

Ho provato ad aggiungere i due indici (uno per idu e uno per titolo) e ad eseguire quella query, ma non termina mai.

No, sono 2 indici, ma sono
(idu, titolo)
e
(titolo, idu)

Sono indici doppi, su 2 colonne.

[Manugal]

Ok, ora funziona e mi ritorna correttamente tutto. Ho aspettato circa 3 minuti e mezzo, cosa che prima non ho fatto (quindi credo che anche prima avrebbe funzionato). Vorrei sapere però che cosa ci avrei guadagnato se avessi avuto (idu,titolo) come chiave primaria. Era più semplice la query?

[gugoXX]

Originariamente inviato da Manugal

Ok, ora funziona e mi ritorna correttamente tutto. Ho aspettato circa 3 minuti e mezzo, cosa che prima non ho fatto (quindi credo che anche prima avrebbe funzionato). Vorrei sapere però che cosa ci avrei guadagnato se avessi avuto (idu,titolo) come chiave primaria. Era più semplice la query?

No, la query era uguale ma il motore SQL, conscio della presenza univoca della coppia di valori, avrebbe potuto prendere altre strade nella soluzione della query, che nel caso sarebbero state sicuramente piu' performanti.

[Manugal]

Ho capito, grazie mille per l'aiuto