Ottimizzazione di una query

[Bukowski]

Salve a tutti,
potrei avere un problema di performance circa la query di posizionamento per il mio gioco.

Spiego subito.

Ho la tabella PUNTI, con 280.000 record, così fatta:

ID | IDUTENTE | PUNTI
(Indici su tutti e 3 i campi.)

Ora, per determinare la posizione di un utente in classifica che ha diciamo 43 punti, faccio cosi:

SELECT punti FROM tbl_punti WHERE status = 'a' AND punteggio > 43;

Ovvero mi trovo quanti record ci sono fino al mio.

Ora, tutto bello, funziona bene, il problema è che facendo analisi mi rendo conto che, ovviamente, un sistema di questo tipo scansiona tutta la tabella, per forza, con questi risultati:

# Query_time: 94  Lock_time: 0  Rows_sent: 120910  Rows_examined: 405849

Devo dire che il tempo di ritorno è solitamente veloce (a parte in momenti di carico, come mostra la riga qui sopra) ma esaminare 405.849 righe e mandarne 120.910 per n utenti credo sia comunque troppo, no?

Volevo chiedere se qualcuno ci è già passato e sa suggerirmi un metodo più performante.

Che mi consigliate????

Grazie!

[optime]

se ti interessa solo sapere QUANTI record vengono restituiti dalla query, usa la COUNT()

[Bukowski]

Si, un pò sono riuscito ad ottimizzarla con il COUNT,
ora scansiona la metà delle righe, che comunque rimane sempre abbastanza.

[lanciafiamme]

Eccome, proprio queste cose sto studiando in 'sti giorni

Se parliamo di mysql (conosco un pochino solo quello ) allora ti servono ovviamente due indici, uno su status e uno su punteggio.

Però c'è un problemino: se le selezioni sono poco... selettive, avrai molti dati tornati e, quindi, la query sarà lenta per forza.

Puoi adottare però un trucchettino, se conosci a priori quante righe ci sono, ovvero fare il totale - (negare la condizione)

Nota che su mysql-innodb il count() è lento in tutti i casi, anche quando conta tutte le righe (senza where).

Se invece usi mysql-myisam allora il count() SENZA where è veloce, e quindi il trucchetto precedente può avere un senso.

[Bukowski]

Ciao Lanciafiamme,
intanto grazie

Sto usando myisam.

La cosa che mi sorprende, banalizzando all'estremo la query, è questa.

Presupponendo di avere appunto una tabella punteggi con 300.000 righe e la colonna punteggi indicizzata, quando vado a fare una query del tipo:

SELECT COUNT(punti) FROM tbl_punti WHERE punteggio > 5;

Mysql ignora completamente l'indice poichè, immagino, essendo il punteggio molto in basso ai risultati e dovendo gioco forza scansionare l'intera tabella per farmi sapere quante righe ci sono, ritiene inutile usare appunto l'indice.

Difatti la cosa cambia quando il punteggio cresce e diventa, che ne so, > 400
in quel caso usa l'indice e le righe scansionate diventano poche migliaia.

A questo punto però mi ritrovo con 250.000 righe scansionate nei casi di minor punteggio (ovvero il 90% dei casi) senza poterlo in qualche modo filtrare adeguatamente.

Possibile che non ci sia un metodo per ottimizzare?

Credo a tal proposito di non aver capito in modo pratico il tuo trucchetto.

Grazie!

[lanciafiamme]

Supponiamo di avere 1000 righe totali, delle quali che so 800 che soddisfano un certo criterio "bianco"
invece di fare "conta tutte le righe che soddisfano il criterio "bianco" "
TOGLI dal totale delle righe quelle che sono NON bianco (nel nostro caso quindi 1000-200).

Questo ha senso se puoi calcolare il totale delle righe velocemente (le 1000) cosa che, come detto, NON funziona con innodb, ma (dovrebbe) funzionare con myisam.

Ti ho buttato lì l'idea, vedi te se può avere un senso nel tuo caso...

[Bukowski]

L'idea sarebbe stata buona, ma il problema è ovviamente (non tanto ) matematico.

Laddove ho la maggiore scansione della tabella - e quindi di intervenire - è proprio quando mi trovo a metà del numero della stessa, cioè quando sono in b: A -- B -- C; entrambe le lunghezze sono più o meno uguali.

Quindi, in una condizione tipo "WHERE punteggio > 5" che è il numero che più si avvicina allo standard (e al problema) mi trovo esattamente a metà della tabella.

Dei 280.000 records, infatti hanno '> di 5' ben 120.000 records, hanno meno di 5 i restanti 160.000.

Poichè la maggior parte di query arrivano con numeri vicini al 5 qui sopra, fa da se che mi ritrovo in una situazione poco felice, dove il bianco e nero da te consigliato (ottima idea) è purtroppo inutile.

Inizio a temere che non ci sia nessun tipo di soluzione.

[lanciafiamme]

Originariamente inviato da Bukowski

Inizio a temere che non ci sia nessun tipo di soluzione.

Una soluzione che mi viene in mente è... tenere i totali.
Ovvero mantenere una tabella che conta i vari dati, magari raggruppati per un certo intervallo, e poi fare un trigger (o da programma) per aggiornarlo quando un punteggio varia.

Così a naso rallenti la fase di inserimento, ma porti a zero quello di conteggio

[Bukowski]

Scusami, non ho capito cosa intendi per mantenere i totali.
Io il totale dei giocatori ce l'ho, essendo myisam, me lo restituisce in tempi zero.

[optime]

lanciafiamme intendeva mantenere una tabella di totali per punti, di questo tipo

TabellaPunti
punti
quanti

da aggiornare ogni volta che fai un inserimento (non so di che, non hai spiegato cosa faccia l'applicativo).

poi invece di interrogare la tabella principale (SELECT COUNT(*) FROM tabella WHERE punti>x) leggi la tabella dei totali (SELECT quanti FROM tabellapunti WHERE punti=x)

spero di aver compreso l'idea di LF