[SQL] Problema di Performance su Query

[ApocalypseNow]

Ciao a tutti!
Ho una query semplice del tipo

"SELECT * FROM UTENTI WHERE nickname = '"& nickname &"'"

che cerca un record nel database utenti a seconda del nickname. NICKNAME non è una chiave primaria, ma essendo che 1 utente può avere 1 solo nickname diverso dagli altri, ho settato la chiave su UNIQUE.

Ora immaginiamo che la tabella utenti conti 150.000 - 200.000 record.
L'effettuazione di questa query sarà molto più lenta rispetto a se ci fossero pochi record nella tabella?

Non sarebbe più veloce fare una ricerca dello stesso record cercandolo con la chiave primaria?

"SELECT * FROM UTENTI WHERE ID = "& ID

...e di quanto migliorerebbe (se migliora) la velocità della query se uso l'ID? Vale la pena modificare la struttura del sito facendo in modo che le ricerche sulla grossa tabella utenti venga fatta per chiave primaria? (il mio problema è che per motivi di sicurezza contro malintenzionati, non vorrei espicitare l'ID dell'utente nei collegamenti).

Attendo una Risposta.
Grazie fin da ora!

[nICO80]

Prendi con le pinze quello che ti dico perchè non sono un esperto, tuttavia credo che sicuramente ci metta di più su un DB grande che su uno piccolo e che inoltre dipenda dal tipo di database che usi (es. InnoDB, MyISAM etc.).
Inoltre penso che mettere un LIMIT 1 alla fine possa velocizzare la cosa

[ApocalypseNow]

Nono ma questo è indubbio Se il DB è grosso è indubbio che ci metta di più! Quel che vorrei sapere, è quanto ci può mettere se si cerca con una chiave unica, e quanto migliorerebbe la cosa se cercassi con una chiave ID primaria.

[nICO80]

Quello che dicevo su myisam/innodb l'avevo leggiucchiato qui
http://www.mysqlperformanceblog.com/...hmarks-part-1/

[skidx]

la chiave primaria è unica, se hai settato lo username con indice UNIQUE è anch'esso unico, quindi la velocità è più o meno identica (ID forse è un filino più veloce perché occupa meno spazio), comunque il tuo dubbio sul fatto che passando l'ID comprometta in qualche modo la sicurezza rispetto al passaggio dello username è privo di fondamento. Entrambi, ID e username, individuano lo stesso record, non vedo cosa cambi.

[andr3a]

Originariamente inviato da nICO80
Inoltre penso che mettere un LIMIT 1 alla fine possa velocizzare la cosa

... LIMIT 1 significa che il tempo della query è esattamente lo stesso, tanto o poco che sia, ma che aspetti dopo la query solo un risultato ed il tempo di fetching ROWS è quindi per ovvi motivi inferiore.

Come fa il db a sapere qual'è quell' UNO da mostrare se non calcolando esatamente tutto quello che avrebbe calcolato senza alcun LIMIT?

[nICO80]

E' per quello che ho detto di prendere quello che dicevo con le pinze!

Io ho sempre supposto che mettendo un LIMIT 1 MySQL cominciasse a fare la ricerca e si fermasse al primo record trovato che soddisfasse le condizioni. In effetti non avevo tenuto conto di cose come ORDER BY...

[daniele_dll]

te la butto li

cercare una stringa, per mysql, comporta parecchio lavoro tra conversioni di codifiche, confronto di carattere per carattere con l'aggiunta che il casing va tenuto in considerazione

i confronti di tipo numerico, invece, sono molto più performanti perché fisicamente il linguaggio macchina li supporta direttamente ... verificare se due stringhe sono uguali e come verificare se una sequenza di numeri sono uguali

ovviamente entrano in ballo una marea di ottimizzazioni che mysql ha applicato quindi devi fare dei test!

puoi provare ad utilizzare un campo affiancato al nickname chiamato magari nickname_hash nel quale vai a inserire il CRC32 del nickname ... ovviamente questo campo deve essere una chiave unica pure lei mentre il tipo deve essere INT 10 NOT NULL di tipo signed (ovvero con numeri negativi e positivi ... ergo senza unsigner) ... ai fini dell'identificazione è indifferente e si evita ulteriore lavoro

Prima che lo tiri in ballo qualche altro ... il CRC32 restituisce valori numerici a 4 byte, ovvero a 32bit, ovvero 4 miliardi e qualcosa di valori ... quindi ci sono più "probabilità" che spuntino dei doppioni rispetto a un MD5 ... però ... anche se è più probabile è comunque NOTEVOLMENTE difficile che accada.

Al limite, dato che in questo specifico caso si tratta di un nickname e quindi di un dato che l'utente può variare, richiedi l'inserimento di un altro nickname

ripeto però ... che comunque c'è la remota possibilità di doppioni ... comunque il mio consiglio è quello prima di provare a verificare se le performance effettivamente aumentano e magari si aggiunge qualche altro valore per identificare il nickname in maniera univoca ... perché utilizzando l'hash come chiave unica usata per la ricerca prima di verificare gli altri valori estrae le chiavi corrispondenti ... quindi il rischio è di dover eseguire il confronto del nickname invece che su una riga ... su due o tre ... o 4 e cosi via

comunque, fai prima la prova

[webus]

Originariamente inviato da daniele_dll
i confronti di tipo numerico, invece, sono molto più performanti perché fisicamente il linguaggio macchina li supporta direttamente ... verificare se due stringhe sono uguali e come verificare se una sequenza di numeri sono uguali

Il guadagno in questo caso potrebbe essere nell'ordine di centesimi di secondo, anche fossimo in presenza di milioni di record. Stiamo parlando di un indice di tipo unico, quindi comunque ordinato e a cui basta trovare 1 ricorrenza.

Se ci sono problemi di performance è perché probabilmente l'ottimizzatore non riesce a impiegare l'indice, e ciò lascierebbe supporre che la query in questione non sia in realtà semplice come quella postata all'inizio (a proposito: che ci stanno a fare le ampersend?).

Comunque dei miglioramenti li si potrebbero ottenere anche accorciando l'indice. Raramente infatti è necessario indicizzare oltre i primi 10-12 caratteri, e nel caso si tratti ad esempio di un char(255) la differenza si farebbe notevole. Ovviamente dovrebbe fare dei test per valutare la lunghezza necessaria.

[daniele_dll]

scusami, ma se accorci il valore da 255 a, per esempio, 12 ottieni comunque un lavoro maggiore rispetto all'uso di un hash numerico settato sempre come chiave indice

inoltre mi pare difficile utilizzi il tipo char per il nickname perché cosi consumerebbe un sacco di spazio inutile (per esempio con un char 255 e 10/12 caratteri realmente usati si sprecherebbe, su 200 mila record, ben 46mb di roba)

altrimenti può spezzare la tabella creandone una di tipo fixed (ovvero niente valori a lunghezza dinamica) dove mette l'id e l'hash ... e altri valori numerici necessari per eventuali query e poi la seconda tabella tramite una inner join con l'id di riferimento alla prima tabella


---
edit
---
pardon, ho letto solo ora che ti riferivi all'indice riguardo allo spazio