Confronto tra tabelle

di
curzio.maria
il
6 risposte
C
curzio.maria
Pochi posts
Iscritto da
ago, 2022
Messaggi:
26

Confronto tra tabelle

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 dli 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

6 Risposte

  • M
    migliorabile
    Super Famoso
    Iscritto da
    apr, 2013
    Messaggi:
    6170

    Re: Confronto tra tabelle

    Poiche' devi confrontare ogni utente con tutti gli altri, se hai N utenti, va da se che devi confrontare N*N utenti

    ora, non serve confrontare un utente con se stesso, quindi ti bastano N*(N-1) confronti.

    Poiche' il confronto di A con B e' uguale a confrontare B con A, il numero di confronti diventa N*(N-1)/2, che comunque e' sempre circa 1/2*N^2.

    in questo caso 600*600/2 = 180_000 confronti.

    Si puo' fare di meglio? Forse. Avevo un'idea ma poi ho capito che non funziona: a te serve il confronto tra ogni coppia di utenti, quindi questo passaggio non si puo' evitate.

    La "complessita computazionale" del problema rimane N^2. E da li non ti schiodi.

  • C
    curzio.maria
    Pochi posts
    Iscritto da
    ago, 2022
    Messaggi:
    26

    Re: Confronto tra tabelle

    Migliorabile, grazie per la cortese risposta, in realtà mi devo scusare per non aver chiarito correttamente il problema, che per fortuna è meno complesso, il confronto è tra l'utente attivo e tutti gli altri, quindi con N utenti = N -1 *.

    In ogni caso è pesantissimo, oltre 40 secondi e limitando il confronto a max 50 utenti per volta, siamo utenti anziani (media >70 anni) rischio che qualcuno mentre attende 'passi oltre', ho provato ad indicizzare tutti i campi coinvolti ma questo, per quanto utile, ha appunto portato il tempo a 40 secondi. 

    Ricordo assai poco di MYSQL, e database in generale, negli ultimi  anni 90   ho lavorato con MsSql e da quanto ricordo non esiste modo diverso dal NOT IN per il confronto che mi serve quindi temo che l'unica soluzione sia eseguire la query con un crono job ogni giorno e utilizzare il risultato salvato in apposita tabella, per il normale uso degli utenti, non avremo risultati aggiornati ma almeno non metterò a rischio le coronarie degli utenti. Grazie ancora per la risposta

  • M
    migliorabile
    Super Famoso
    Iscritto da
    apr, 2013
    Messaggi:
    6170

    Re: Confronto tra tabelle

    Una cosa che puoi fare e' controllare SE vengono usati correttamente gli indici.

    Devi analizzare la query con il 'query plan' e controllare che i 'full scan' (ricerca sequenziale) siano applicati SOLO a casi dove effettivamente ha senso.

    SEMBRA complicato ma non lo e'.

    https://dev.mysql.com/doc/refman/8.4/en/execution-plan-information.html

    ASSIOMA: 40 secondi non sono tempi accettabili. Potrebbero essere accettabili con tabelle da MILIARDI di record, non con decine/centinaia di migliaia.

    Quindi c'e' qualche pasticcio di qualche genere.

    Potrebbe anche essere un problema hardware: poca ram, disco lento, cpu che funziona a frequenza piu' bassa di quella prevista, ecc

    Controlla ed investiga meglio.

  • S
    sihsandrea
    Molto attivo
    Iscritto da
    feb, 2011
    Messaggi:
    1880

    Re: Confronto tra tabelle

    Secondo me stai sbagliando logica di visualizzazione.

    mi metto nei panni di un utente collezionista.

    1) metto in vendita/scambio i doppioni

    2) cerco l'elenco dei doppioni disponibili all'acquisto/scambio degli utenti associati.

    per il punto 1 metto in tabella "doppie" le mie doppie (la tabella potrebbe non esistere ma estrapolata dalla tabella collezioni where quantità > 1 and utente=utenteloggato (io))

    il punto 2 sarebbe la stesa sql con utente<>utenteloggato (io))

    quando cerco una cartolina la query mi mostra le q.tà disponibili da parte degli altri utenti. 

    es. 

    idCartolina    descrizione    miniaturaimmagine    disponibilità   tipodisponibilità(vendita/scambio)

    lato molti (o selezionando una cartolina) vedrei chi la detiene e se vende o scambia (se la mette su doppioni è per vendere o scambiare).

    in questo caso avrei come record il numero di record <= al numero di record del catalogo e la quantità che è la somma dei doppioni degli utenti (al netto degli idcartolina in mio possesso)

    nel dettaglio potrei anche inviare un messaggio all'utente/utenti se sono interessato all'acquisto/scambio.

    a che serve una tabella in risposta N*N?

    al massimo una right join where null per ottenere i risultati della tabella lato destro al netto dei campi in comune (dove i campi in comune sono le cartoline in mio possesso).

    morale della favola: rivedi la logica che stai usando.

  • C
    curzio.maria
    Pochi posts
    Iscritto da
    ago, 2022
    Messaggi:
    26

    Re: Confronto tra tabelle

    Buongiorno e grazie per i suggerimenti.

    @migliorabile. Seguirò sicuramente i tuoi preziosi consigli, concordo: i tempi sono inaccettabili. La via maestra sono probabilmente gli indici, inoltre ma non posso farci affidamento, la macchina locale che uso è sicuramente lenta, CPU 2500k e4 Gb RAM, il sito è collocato un VPS con 8CPU e 16Gb di RAM disco NVMe, che è sicuramente più performante, è possibile che in remoto i tempi migliorino, a fine settimana finalmente avrò le credenziali di accesso e potrò fare delle prove. Per il momento grazie.

    @sihsandrea. I collezionisti sono gente particolare, ancor più quando hanno una età media molto alta.  

    Come ragiona il (nostro) collezionista:   "Controllo se alcune delle mie doppie mancano ad altri collezionsti e le doppie di altri collezionsiti mancano a me", poichè i collezionisti non sono tutti uguali (come numeri in collezione/doppie) avendo un elenco che elenca le corrispondenze ( SUE che mi mancano - MIE che mancano a lui) lo scambio è enormemente agevolato non perdo tempo a controllare un collezionista che NON ha doppie che mancano a me o a cui non mancano le mie doppie. La nostra filosofia è lo scambio, per la vendita sicuramente questi 'paletti' verrebbero meno. Quindi con 500 collezionisti avere un elenco simile facilita enormente gli scambi e purtroppo non esiste soluzione diversa, meglio soluzione diversa alla mia portata. Grazie per la risposta 

  • S
    sihsandrea
    Molto attivo
    Iscritto da
    feb, 2011
    Messaggi:
    1880

    Re: Confronto tra tabelle

    25/08/2025 - curzio.maria ha scritto:

    Come ragiona il (nostro) collezionista:   "Controllo se alcune delle mie doppie mancano ad altri collezionsti e le doppie di altri collezionsiti mancano a me", poichè i collezionisti non sono tutti uguali (come numeri in collezione/doppie) avendo un elenco che elenca le corrispondenze ( SUE che mi mancano - MIE che mancano a lui) lo scambio è enormemente agevolato non perdo tempo a controllare un collezionista che NON ha doppie che mancano a me o a cui non mancano le mie doppie.

    una ragione in più per evitare quella unione che non dice chi ha la mia cartolina mancante a fronte di una mia mancante all'altro.

    dovresti selezionare le mancanti dal catalogo e man mano che lo scorri vedere a dettaglio chi la detiene e se ho qualcosa da offrire in cambio.

    non puoi estrapolare tutto ti ritrovi con N record di N utenti (con doppie ammesse perchè la cartolina X può averla sia pippo che pluto che cesare che battista).

    non puoi fare un calderone di dati che diventerebbero più incomprensibili rispetto al pulsante mostra solo chi ha la cartolina X

    e da li un detail che mostra le cartoline mancanti.

    devi sempre procedere con più query (anche se in cascata).

    credo che un hobby non valga la pena aspettare 40 minuti per sapere che non posso effettuare scambi.

    esisterebbe anche la possibilità di scambiare un doppio con una già in possesso ed avere un doppio da poter scambiare.

    pippo detiene B e cerca A

    pluto ha C e cerca B

    battista ha A e cerca C

    pippo scambia B con pluto e ottiene C che scambia con battista per ottenere A...

    io farei uso di combo o pulsanti di ricerca in cascata.

Devi accedere o registrarti per scrivere nel forum
6 risposte