Perchè gli indici aumentano la velocità delle ricerche?

[mirko000]

Poniamo il caso che ho 2 tabelle, A e B in join l una con l altra, tramite il campo X.
Se X vale 'pippo' la join, in concreto andrà a controllare record per record fino a che non troverà il record che ha il valore X='pippo' sia nella tabella A che nella tabella B.

Se facessi un indice sul campo X, vorrebbe dire che il db si creerebbe una tabella di indici nella quale avrà per ogni valore di X il numero del record in cui si trova nella tabella A.

A questo punto la ricerca dovrebbe essere più veloce giusto? Perchè avendo il numero del record su cui si trova ci va secca, ecco, non capisco questo.

A livello concreto sposta il loop che c era per cercare il valore 'pippo', dalla tabella A alla tabella degli indici creata quindi non c è nessun aumento di velocità !

Aspetto un chiarimento, grazie

Mirko

[philbert]

Originariamente inviato da mirko000
A livello concreto sposta il loop che c era per cercare il valore 'pippo', dalla tabella A alla tabella degli indici creata quindi non c è nessun aumento di velocità !

Aspetto un chiarimento, grazie

Mirko

A parte il fatto che la tabella degli indici è più piccola della tabella A perché contiene solo gli indici...
ma sopratutto la tabella degli indici è ordinata!!!!
questo significa che quando fai una ricerca puoi fare una ricerca binaria anziché una ricerca campo per campo, il che è assai più veloce!!!

immagina di avere un libro senza indice degli argomenti...per trovare l'argomento che ti interessa devi sfogliare tutto il libro...
se invece hai l'indice degli argomento intanto hai molte meno parole da controllare...
e poi le parole sono in ordine alfabetico e quindi se cerchi "database" qando leggi "server" sai che devi andare a guardare nelle parole prima e non in quelle dopo...

non so se mi sono spiegato...


ricerca binaria:
http://it.wikipedia.org/wiki/Ricerca_dicotomica

[mirko000]

Sei stato chiaro !

Finalmente ho capito perchè va più veloce !

A questo punto non mi converrebbe indicizzare tutti i campi ?

Scusa la mia curiosità !

[philbert]

Originariamente inviato da mirko000
A questo punto non mi converrebbe indicizzare tutti i campi ?

No, e questo almeno per tre motivi:
1) la tabella indici sarebbe gigantesca, e quindi la ricerca sarebbe più lenta
2) gli indici velocizzano la ricerca dei dati ma ne rallentano l'inserimento, l'aggiornamento e la cancellazione, perché a ogni modifica devono essere aggiornati
3) gli indici vengono di fatto utilizzati solo per i campi presenti nelle where, che tipicamente sono 2-3...

quindi in sostanza è inutile e dannoso mettere degli indici su campi che presumibilete non compariranno mai come criteri di ricerca...

[mirko000]

E il campo della join..

[franzauker2.0]

Bhè iniziamo a dire che su un qualsiasi RDBMS moderno gli indici sono tipicamente basati su foglie, non esiste il concetto di ricerca binaria (in realtà esiste, ma tipicamente per piccole cardinalità su elenchi brevi, ad esempio gli IN statici di mysql).

Detto questo aggiungiamo che esistono anche indici NON ordinati, e non ordinabili.

Poi diciamo pure che il tipo di indice è importante per la tipologia di utilizzo, scelta dall'ottimizzatore, e quindi avranno senso indici puntuali (es. hash) per usi puntuali (es. equi-join), mentre hanno senso indici ordinati (es. btree) per raggruppamenti.

Poi diciamo che la selettività dell'indice determina in gran parte l'utilità del medesimo.

Poi diciamo - basta fine compilazione -

Sintetizzando: la scelta "migliore" degli indici richiede una conoscenza della struttura dei dati, e di come vengono interrogati e manipolati, oltre a parecchio background sul programma che si utilizza e magari un ottimo profiler per controllare cosa accade.

[mirko000]

Poi diciamo che non ho capito niente di quello che hai detto..

[franzauker2.0]

Originariamente inviato da mirko000
Poi diciamo che non ho capito niente di quello che hai detto..

Diciamo che ci vogliono un 15-20 anni per un certo livello di consapevolezza (tipo Matrix).

Vabbè cominciamo dalle superbasi: immagini semplicemente l'indice di un libro.
Se vuoi sapere a che pagina si trova il capitolo "pippo" ti basta leggere una quantità di dati molto minore (l'indice appunto) rispetto alla tabella (le pagine vere e proprie).

Questo risponde alla tua domanda, a un livello dilettantistico.

A un livello meno (ma sempre nella fascia home) immagina cosa succede se crei una riga-indice PER OGNI riga-tabella.
In questo caso avresti un indice talmente grande che leggerlo non ti darebbe vantaggi sensibili rispetto a leggere la tabella intera (il c.d. full scan).

Questa è la "selettività", che è un elemento fondamentale (sempre in fascia home) per l'utilizzo degli indici, e la scelta di quando e come e se usarli.

Supponiamo di avere un elenco di anagrafiche (nome, cognome, genere), TUTTI maschi, e supponiamo di creare un indice sull'attributo genere.

Una interrogazione del tipo "cerca tutti i maschi", eseguita sull'indice, è più lenta dell'esame di tutte le righe, senza avere l'indice del tutto, perchè c'è da smazzarsi tutto l'overhead di gestione dell'indice, ma ritornando poi tutte le righe.

Questo esempio banale spero ti chiarisca, sia pure a livello introduttivo, che la questione è complessa assai.

A livello professionale è talmente complessa che, normalmente, dopo un'analisi teorica ci si occupa di una... sperimentazione, ovvero profilando (orribile traduzione di "profiling") e misurando le prestazioni del sistema.

Poi c'è tutto il discorso relativo al tipo di indice, dipendente dal tipo di ricerca.
"dammi tutti i maschi" è una interrogazione
"dammi tutti i maschi che si chiamano alberto" è un'altra
"dammi tutti i maschi nati dopo il 1972" un'altra ancora
"dammi tutti quelli che hanno codice fiscale ccccccccccc" è ancora diversa
"fai il join tra la tabella X e la Y"

sono tutti casi diversi che richiedono approcci diversi (nel caso si voglia operare qualcosa di serio e non homesco)