[MySql/PHP] Ottimizzazione algoritmo aggiornamento tabella

[GHz]

Ciao a tutti,
avrei bisogno di un consiglio di qualcuno esperto di mysqp/php per ottimizzare un algoritmo di aggiornamento di un a tabella. Spiego come funziona.

C'è una tabella dei progetti e una degli utenti.

Per ogni utente, ci sono diversi record nella tabella utenti in base ai progetti seguiti dall'utente. Quindi se un utente segue 2 progetti, nella tabella utenti ci saranno 2 record di quell'utente.
Ad ogni utente, per ciascun progetto è associato un punteggio. Quindi la tabella utenti è fatta in questo modo:

Codice_Utente (VARCHAR 32), Punteggio (DOUBLE), Codice_Progetto ( INT 3)

Il Codice_Progetto è un intero, invece il Codice_Utente è una stringa (un hash md5), che identifica univocamente l'utente sui vari progetti.

Per fare le statistiche sui punteggi, devo sapere la posizione di ciascun utente in un singolo progetto oppure considerando tutti i progetti, facendo la somma dei punteggi sui vari progetti raggruppando per Codice_Utente. Per questo ho aggiunto 2 campi alla tabella, uno chiamato Pos_Progetto e un'altro Pos_Totale, che aggiorno con uno script.
Per l'aggiornamento del campo Pos_Progetto non c'è problema, è molto veloce (meno di un secondo), invece per l'aggiornamento di Pos_Totale ci mette diversi minuti.
Lo script legge la tabella in ordine di punteggio raggruppando per hash e per ogniuno va a scrivere il campo Pos_Totale. Io penso che sia lento perchè la ricerca sul campo di tipo stringa è molto lenta, mentre la ricerca per quello numerico (nel caso di Pos_Progetto avviene su un intero che non ho riportato in questo esempio per semplificare) è velocissima.
La tabella degli utenti contiene attualmente circa 9000 record, e raggruppando per utente vengono circa 5000 record. Andando a vedere il tempo di esecuzione dello script in dettaglio, per aggiornare la posizione totale di un singolo utente ci mette circa 0,1 secondi, per farne 100 ci impiega infatti circa 10 secondi. Per aggiornarli tutti ci mette una vita.
Vi riporto lo script incriminato:

//aggiorna il rank degli utenti in classifica combinata
function updatecombinedranks()
{
	$sql = "SELECT SUM(Punteggio) as TC, Codice_Utente FROM tabellautenti GROUP BY Codice_Utente ORDER BY TC DESC";
	$combined = query_fetch($sql);
	$count = count($combined);
	echo "COUNT = ". $count."<br/>";
	for ($i = 0; $i<$count ; $i++)
	{
		$sql = "UPDATE tabellautenti SET Pos_Totale = ".($i+1)." WHERE Codice_Utente = \"".$combined[$i]['Codice_Utente']."\"";
		if (!mysql_query($sql))
		{
			echo "Errore su Codice_Utente = ".$combined[$i]['Codice_Utente'];
		}
    }
}

C'è un modo per ottimizzare questa procedura? Qualcuno ha già affrontato un problema simile?

Grazie, ciao,
GHz

[beppegrillo]

Non comprendo i motivi per il quale utilizzi come chiave un hash di una stringa, e non cresi semplicemente una nuova tabella ID,NOME_UTENTE a cui le altre faranno riferimento come chiave esterna.

[GHz]

Originariamente inviato da beppegrillo

Non comprendo i motivi per il quale utilizzi come chiave un hash di una stringa, e non cresi semplicemente una nuova tabella ID,NOME_UTENTE a cui le altre faranno riferimento come chiave esterna.

Non è che mi voglio fare del male è che i dati che ho a disposizione sono in quel formato.
Ogni progetto è indipendente e ogni utente ha un suo id nel progetto (intero, che non ho riportato prima per semplificare e che uso per aggiornare il campo Pos_Progetto), e questo hash per identificare l'utente tra i vari progetti.

[GHz]

UP, nessuno ha qualche idea/suggerimento?

[gugoXX]

Ti scrivo una possibile soluzione per Oracle e una per Sql-Server, dato che MySql non lo tocco da un po' e le funzioni analitiche non so se sono completamente supportate.

SQL-Server : 

UPDATE tabellautenti
FROM (
	SELECT Codice_Utente, 
            ROW_NUMBER() OVER (ORDER BY TC DESC) pos FROM (
	        SELECT SUM(Punteggio) as TC, Codice_Utente 
                          FROM tabellautenti GROUP BY Codice_Utente
        )
) src
SET tabellautenti.Pos_Totale = src.pos
WHERE tabellautenti.Codice_Utente = src.Codice_Utente

Oracle:

UPDATE 
(
SELECT tabellautenti.Codice_Utente, tabellautenti.Pos_totale,
           ROW_NUMBER() OVER (ORDER BY TC DESC) pos FROM (
	   SELECT Codice_Utente, SUM(Punteggio) as TC  FROM 
                    tabellautenti GROUP BY Codice_Utente
        ) src
       JOIN tabellautenti on tabellautenti.Codice_Utente = src.CodiceUtente)
SET Pos_totale = pos;

PS: Ho scritto al volo, quindi puo' esserci qualche errore di sintassi, ma il concetto e' che consiglio di fare tutto in un unico statement di Update, senza conivolgere il linugaggio imperativo di turno e dando un occhio all'ottimizzatore.

[GHz]

Originariamente inviato da gugoXX

Ti scrivo una possibile soluzione per Oracle e una per Sql-Server, dato che MySql non lo tocco da un po' e le funzioni analitiche non so se sono completamente supportate.

PS: Ho scritto al volo, quindi puo' esserci qualche errore di sintassi, ma il concetto e' che consiglio di fare tutto in un unico statement di Update, senza conivolgere il linugaggio imperativo di turno e dando un occhio all'ottimizzatore.

Molto interessante la tua soluzione, vedo che di database te ne intendi
Avevo visto tempo fa qualche articolo in rete che spiegava come fare gli aggiornamenti in mysql in un unico statement di update, però era un pò più complicato perchè mysql non ha la funzione per sapere la posizione del record (ROW_NUMBER) e va simulata con delle variabili.....devo studiarci un pò e fare qualche prova.
Intanto grazie, se hai qualche altro consiglio/esempio posta pure!

Ciao,
GHz