• User Attivo

    query incrociata per classifiche personali

    Salve a tutti,
    spero di trovare aiuto (per gli esperti probabilmente stupido) ad una query che non riesco a fare.

    Vado subito a spiegare.

    Per il mio sito di giochini ho 2 tabelle, 1 in cui raccolgo l'account dell'utente, l'altra in cui scrivo i punteggi realizzati nei vari giochi.

    Quindi ho questa situazione:

    TABELLA ACCOUNT

    | iduser | nick | pwd

    TABELLA registro PUNTEGGI

    | idnick | gioco | punteggio | data

    Come faccio ora a visualizzare la posizione del giocatore gioco per gioco in un'unica schermata?

    Es.
    Scheda riassuntiva dell'user BUKOWSKI

    ARKANOID - 4° su 130
    CALCIO - 15° su 120
    TETRIS - 6° su 100

    ecc ecc

    Considerando che la tabella "ACCOUNT" ha 20.000 recordset e quella "PUNTEGGI" 90.000

    Ringrazio anticipatamente a chi vorrà aiutarmi 🙂


  • User

    @Bukowski said:

    Come faccio ora a visualizzare la posizione del giocatore gioco per gioco in un'unica schermata?

    Se ho capito bene quello che vuoi fare penso che GROUP BY sia quello che fa per te.


  • User Attivo

    @nessuno said:

    Se ho capito bene quello che vuoi fare penso che GROUP BY sia quello che fa per te.

    non credo che il GROUP BY possa essermi d'aiuto 😞
    poichè debbo stabilire in qualche modo la posizione che il giocatore ha per ogni gioco.
    Forse usando i join ?


  • User

    @Bukowski said:

    non credo che il GROUP BY possa essermi d'aiuto 😞
    poichè debbo stabilire in qualche modo la posizione che il giocatore ha per ogni gioco.
    Forse usando i join ?

    una join ti serve di sicuro per collegare le due tabelle.


  • User Attivo

    il mio problema è proprio sulle JOIN
    qualcuno sa aiutarmi? 😄


  • User

    @Bukowski said:

    il mio problema è proprio sulle JOIN
    qualcuno sa aiutarmi? 😄

    se butti giu' un DESCRIBE delle due tabelle e cosa vuoi fare effettivamente potrei essere piu' preciso.

    Ad occhio una cosa di questo tipo dovrebbe fare al caso tuo (se i punteggi sono gia sommati altrimenti occorre SUM(punteggio))

    SELECT nick, gioco, punteggio
    FROM nome_tabella_account
    LEFT JOIN nome_tabella_punteggi
    ON iduser = idnick
    GROUP BY gioco
    ORDER BY punteggio DESC

    In alcuni casi una INNER JOIN potrebbe essere piu' indicata di una LEFT JOIN

    Se vuoi ordinare per nick ORDER BY nick ASC


  • User Attivo

    Innanzitutto grazie per l'aiuto 😉

    Purtroppo la query è troppo lenta e pensa non ce la fa neanche ad essere eseguita 😞 ... mi va in saturazione il mysql. E cmq non credo facesse quello di cui necessitavo.

    Non so come spiegarmi meglio di come ho già fatto sotto.

    In pratica ho 2 tabelle:
    # TABELLA ACCOUNT (20.000 record)

    • Qui ci sono i dati di registrazione dell'utente
      | iduser | nick | pwd
      esempio: 5 | Bukowski | peppino

    **# TABELLA PUNTEGGI ** (90.000 record)

    • Qui registro i punteggi fatti da ogni utente in ogni gioco
      | nickpt | gioco | punteggio | data
      esempio: | Bukowski | Arkanoid | 34000 | 12-12-2005
      esempio: | Nessuno | Flipper | 15000 | 10-12-2005

    Ora vorrei avere un quadro generale per ogni singolo utente, in quali giochi ha giocato e che piazzamento ha avuto.

    Esempio su utente Bukowski

    • Arkanoid - ha fatto 34000 ed è alla posizione 28
    • Bubble - ha fatto 12000 ed è alla posizione 125

    Il problema è che come la faccio la faccio viene pesantissima tanto da saturarmi il DB .... una cosa uguale la fa anche il VBullettin Addon Giochi, ma la query è velocissima.

    Ti ringrazio per la tua disponibilità 🙂


  • User

    Ok... adesso ho letto meglio ed e' piu' chiaro.

    Per la velocita' hai assolutamente bisogno di mettere due indici sulle tabelle,
    PRIMARIO su nome_tabella_account.iduser

    Questa e' la query base
    SELECT gioco, punteggio
    FROM nome_tabella_account
    LEFT JOIN nome_tabella_punteggi
    ON iduser = idnick
    WHERE iduser = <id_dell'utente>

    altrimenti
    WHERE nick = <nick_dell'utente>
    ma perde moltissimo in velocita'

    Per la posizione la cosa si complica... sconsiglierei di calcolarla in ogni singola query perche' alzerebbe esponenzialmente la complessita' dell'elaborazione.

    Ti conviene aggiungere un campo nella tabella punteggi e trovare una soluzione per calcolarla prima. Su questo dovrei ragionarci un po' su.


  • User Attivo

    Ok, in questo caso però non ho fatto altro che ottenere tutti i giochi ai quali ha giocato IDUTENTE ... non c'è bisogno di fare un LEFT JOIN perchè se è vero che conosco l'id mi basta fare:
    SELECT gioco, punteggio
    FROM tbl_punteggi
    WHERE nick = $nick

    e ottengo lo stesso risultato.

    La cosa bella sarebbe determinare in che posizione è l'utente rispetto agli altri. Mi sembra strano che debba fare tante query quante sono i giochi.

    Ti ringrazio per l'aiuto e non vorrei abusare della tua gentilezza, quindi fermati quando vuoi 😉

    Grazie ancora


  • User

    @Bukowski said:

    La cosa bella sarebbe determinare in che posizione è l'utente rispetto agli altri. Mi sembra strano che debba fare tante query quante sono i giochi.

    Io userei

    SELECT nick, gioco, punteggio
    FROM nome_tabella_account
    LEFT JOIN nome_tabella_punteggi
    ON iduser = idnick
    WHERE iduser = <id_dell'utente>

    (cosi' avresti anche il nick... altrimenti puoi usare la tua query, ma il nick deve saltare fuori in altro modo)

    Poi per la posizione
    SELECT COUNT(*) FROM tbl_punteggi WHERE punteggio > <punteggio_utente> (lo passi via PHP)

    altrimenti se hai a disposizione una versione MySQl che supporta subqueries (> 4.1)
    SELECT COUNT(*) FROM tbl_punteggi WHERE punteggio > (SELECT punteggio FROM tbl_punteggi WHERE iduser = <id_dell'utente>)

    Una cosa del genere dovrebbe funzionare

    ciao


  • User Attivo

    Ok mi metto subito all'opera.

    Grazie ancora per il prezioso supporto.

    😉


  • User Attivo

    Allora secondo me ci siamo molto vicini, però non capisco una cosa ... dovrei fare tante query quanti sono i giochi per contare la posizione?

    Con questa query tiro fuori, di un determinato utente, tutti i giochi e i punteggi realizzati:
    SELECT nickpt, giocopt, puntipt
    FROM mp_classifiche WHERE nickpt= '$user';
    il risultato è giustamente:
    | BUKOWSKI | ARKANOID | 100.000 |
    | BUKOWSKI | FLIPPER | 755.000
    ecc ecc.

    E' l'altra che non so dove e come applicarla 😞

    Poi per la posizione
    SELECT COUNT(*) FROM tbl_punteggi WHERE punteggio > <punteggio_utente> (lo passi via PHP)

    Si ripeterà nel ciclo while di ogni riga per ogni gioco??

    Grazie ancora

    (Purtroppo ho del mysql la 4.0)


  • User

    Ci sono diversi metodi per affrontare il problema.

    Per esempio:
    preso un utente fai una query che ottenga nick, gioco e punteggio e le conservi in alcune variabili. Poi all'interno del loop chiami la query

    SELECT COUNT(*) FROM tbl_punteggi WHERE punteggio > <punteggio_utente> AND gioco = <gioco>

    e poi mandi in stampa tutto quello che ti serve.


  • User Attivo

    @nessuno said:

    Ci sono diversi metodi per affrontare il problema.

    Per esempio:
    preso un utente fai una query che ottenga nick, gioco e punteggio e le conservi in alcune variabili. Poi all'interno del loop chiami la query

    SELECT COUNT(*) FROM tbl_punteggi WHERE punteggio > <punteggio_utente> AND gioco = <gioco>

    e poi mandi in stampa tutto quello che ti serve.

    ok .. quindi però mi ritrovo ad avere tante query quante sono i giochi per quel determinato utente .. ovvero 40 ...

    ho paura che ritorno ad una situazione di pesantezza della query.


  • User

    Come ti dicevo prima ci sono sicuramente molti altri modi di affrontare questo problema, alcuni radicalmente diversi.

    IMHO anche 40 queries di quel tipo (vedi successivi commenti) non sono particolamente pesanti se il server e' correttamente dimensionato; sarebbero molto peggio un numero di queries inferiori ma molto piu' complicate.

    Ci sono altre valutazioni da fare sulla struttura del database e sull'ottimizzazione delle queries.

    Ti posto in seguito alcuni commenti.


  • User

    Per quanto riguarda la struttura del database

    @Bukowski said:

    In pratica ho 2 tabelle:
    # TABELLA ACCOUNT (20.000 record)

    • Qui ci sono i dati di registrazione dell'utente
      | iduser | nick | pwd
      esempio: 5 | Bukowski | peppino

    **# TABELLA PUNTEGGI ** (90.000 record)

    • Qui registro i punteggi fatti da ogni utente in ogni gioco
      | nickpt | gioco | punteggio | data
      esempio: | Bukowski | Arkanoid | 34000 | 12-12-2005
      esempio: | Nessuno | Flipper | 15000 | 10-12-2005

    In accordo con le regole di [url=http://it.wikipedia.org/wiki/Normalizzazione_del_database]normalizzazione di un database, in particolare, in questo caso, la Seconda Forma Normale, la tabella tbl_punteggi andrebbe scomposta in due tabelle con la seguente struttura:

    tbl_punteggi
    | nickpt | gioco_id | punteggio | data (assumo che nickpt sia un indice che si riferisce alla tabella degli utenti)

    tbl_giochi
    gioco_id | gioco_name

    Questo implica un serie notevole di vantaggi:

    • nella clausola WHERE non metti un nome ma un numero per cui la query richiede meno elaborazione, per cui termina piu' rapidamente.
    • nei 90000 records della tbl_punteggi avrai solamente dei numeri (tinyint da 1 byte possibilmente) invece che lettere (VARCHAR da 8 + 1 byte nel caso di Arkanoid)
    • se vuoi cambiare un nome di un gioco, lo cambi una sola volta e non in tutte le sue occorrenze fra quelle 90000 righe
    • e in ultimo... si fa cosi' ; )

  • User

    Con la struttura definita come nel precedente messaggio, sarebbe utile avere degli indici su:

    tbl_account.iduser (PRI)
    tbl_giochi.gioco_id (PRI)
    tbl_punteggi.nickpt (INDEX)
    tbl_punteggi.gioco_id (INDEX)

    Eventualmente io consiglierei di utilizzare tabelle INNODB e settare le foreign_keys.

    In questa maniera tutte le join verrebbero fatte su dei campi indicizzati per cui il carico elaborativo sarebbe minimo.

    Ulteriori ottimizzazioni potrebbero essere ottenute scegliendo adeguatamente i tipi di dati di ogni campo.

    Se posti l'output di un
    DESCRIBE nome_tabella
    oppure SHOW CREATE nome_tabella
    ti posso dare maggiori dettagli


  • User Attivo

    Accidenti, mi ero perso il proseguio di questo post .... 😮

    io devo mettermi a studiare perchè quello di cui hai scritto ho capito il 70%.

    A questo punto se dici che è meglio avere degli INT potrei trasformare la tabella punteggi in
    IDNICK (ora c'è il nome!) | IDGIOCO | PUNTEGGIO
    es: 6 | 9 | 54.000

    e quindi avere una tabella che riporta i nomi dei giochi
    IDGIOCO | NOMEGIOCO
    es: 1 | Arkanoid

    E' anche vero che cosi facendo mi troverò costretto a più parti a ricorrere a dei JOIN sia per ricavare il nome del gioco che il nick del giocatore.

    Giusto?


  • User

    @Bukowski said:

    E' anche vero che cosi facendo mi troverò costretto a più parti a ricorrere a dei JOIN sia per ricavare il nome del gioco che il nick del giocatore.

    Giusto?

    Si, ma ma avrai un database fatto secondo le regole e le prestazioni saranno superiori.

    E considera che le join su un database cosi' semplice sono un banalita'.