Confronto tra tabelle

[kairos2020]

Buongiorno, curo un sito di collezionisti di cartoline, dovrei preparare una tabella che confrontando le collezioni e le doppie di ogni collezionista mi mostri la relazione doppie - mancanti tra ognugno di loro.

Le tabelle in gioco sono due : collezioni dove sono registrate le collezioni e doppie, dove ogni collezionista registra le sue doppie.

La tabella COLLEZIONI è così composta : ID (pk id riga) USER_ID (fk id utente ) CART_ID (Fk id cartolina in catalogo)
La tabella DOPPIE è così composta : ID ID (pk id riga) USER_ID (fk id utente ) CART_ID (Fk id cartolina in catalogo)
Sono uguali.
Ora per il confronto, tra due utenti, per tutti userò un ciclo (credo) uso la seguente query:

SELECT COUNT(*), `user_id` AS CONTA FROM `doppie` WHERE `user_id`=122 And`special_id`=1 AND `capsule_id` NOT IN (SELECT `capsule_id` FROM collezioni WHERE `user_id`=98)
UNION
SELECT COUNT(*) AS CONTA, `user_id` FROM `doppie` WHERE `user_id`=98 And`special_id`=1 AND `capsule_id` NOT IN (SELECT `capsule_id` FROM collezioni WHERE `user_id`=122)

Funziona,se confronto tra loro due collezionisti da oltre 10.000 cartoline impiega circa 0.05 secondi, se ne confronto due da circa 3.000 cartoline impiega 0,03.

Domando esiste un modo più efficiente per un lavoro simile ? mi chiedo poi, esiste un modo più veloce che utilizzare un ciclo in PHP per confrontare tra loro circa 600 utenti, oggi il ciclo impiega circa 8 secondi.

Grazie in anticipo

[alemoppo]

Non ho capito al 100% cosa è special_id. In ogni caso, per ricavare il confronto esegui 4 query.
Mi sono aiutato con la IA (sono molto arrugginito con le queries), ma prova se questa risulta più efficiente:

SELECT doppie.user_id, COUNT(*) AS conta
FROM doppie
LEFT JOIN collezioni 
  ON collezioni.user_id = 98
 AND collezioni.capsule_id = doppie.capsule_id
 AND collezioni.special_id = doppie.special_id
WHERE doppie.user_id = 122
  AND doppie.special_id = 1
  AND collezioni.id IS NULL
GROUP BY doppie.user_id

UNION ALL

SELECT doppie.user_id, COUNT(*) AS conta
FROM doppie
LEFT JOIN collezioni 
  ON collezioni.user_id = 122
 AND collezioni.capsule_id = doppie.capsule_id
 AND collezioni.special_id = doppie.special_id
WHERE doppie.user_id = 98
  AND doppie.special_id = 1
  AND collezioni.id IS NULL
GROUP BY doppie.user_id;

Per il confronto tra tutti gli utenti, dovresti riuscire a farlo tutto tramite mysql, dovrebbe essere molto più veloce. Una cosa del genere:

SELECT doppie.user_id     AS da_utente,
       collezioni.user_id AS a_utente,
       COUNT(*)           AS conta
FROM doppie
JOIN collezioni 
  ON collezioni.special_id = doppie.special_id
LEFT JOIN collezioni AS check_collezioni
  ON check_collezioni.user_id = collezioni.user_id
 AND check_collezioni.capsule_id = doppie.capsule_id
 AND check_collezioni.special_id = doppie.special_id
WHERE doppie.user_id <> collezioni.user_id
  AND check_collezioni.id IS NULL
GROUP BY doppie.user_id, collezioni.user_id
ORDER BY doppie.user_id, collezioni.user_id;

Ciao!

[kairos2020]

Alemoppo grazie. Il campo special_id è un flag, indica il tipo di cartolina, 1 per STORICA, 2 per ANIMALI e così via. Facendo delle prove la query con AND su questo campo raddoppia il tempo necessario. Proverò subito la soluzione da te proposta e a seguire ti farò sapere il risultato. Per il momento ancora grazie. Mauro

[kairos2020]

Ciao Alemoppo, ho provato quanto da te suggerito ma purtroppo non migliora la situazione, ho fatto 10 selezioni usando la tua query, il tempo medio di risposta è 5,2456 secondi, la query originale ha avuto come risultato un tempo medio di risposta pari a 0,304 secondi. Non credo di aver fatto errori durante i test, ho solo copiato la tua query. Probabilmente il risultato è influenzato dagli indici. Volevo utilizzare la query per mostrare ad ogni utente la propria situazione rispetto ad ogni altro, credo che l'unica soluzione sia eseguire la query in un momento di tranquillità, di notte, e poi utilizzare per tutto il giorno seguente i risultati così ottenuti, non avrò una tabella aggiornatissima ma non fermerò il sito per 'ore' ( 15 minuti circa). Giusto per statistica la tabella collezioni ha 2.342.344 record e la tabella doppie 474.876 record e gli utenti sono 546, numeri non eccezionali ma neppure banali. Grazie

[dreadnaut]

Se ho capito bene, vuoi confrontare la collezione di un utente (Aldo) con tutte le capsule doppie che gli altri sarebbero disposti a cedere o vendere. Così puoi dire ad Aldo: ti mancano queste capsule, e puoi chiederle a queste persone. Ma vuoi fare anche l'opposto, cioè mostrare ad Aldo a chi può dare le sue doppie?

Per la prima query, prendile tutte assieme, invece di un singolo utente:

SELECT * FROM doppie
  WHERE user_id <> :aldo
    AND capsule_id NOT IN (
      SELECT capsule_id FROM collezioni WHERE user_id = :aldo
    )

Se ti server, raggruppi per 'offerente' in PHP:

$disponibili = $db->esegui($query, [ 'aldo' => 12345 ]);

$perOfferente = [];
foreach ($disponibili->fetchAll() as $capsula) {
  $id = $capsula->user_id;
  $perOfferente[$id] ??= [];
  $perOfferente[$id][] = $capsula;
} 

La seconda operazione, "a chi puoi dare le tue doppie", sarà sempre costosa perché non hai un concetto di "mancante" e ti tocca ricalcolarlo per ogni utente. Sarebbe più facile se i tuoi utenti potessero marcare quali capsule gli interessano, e poi confronti doppie e ricercate.