Newsgroup: it.comp.software.database
Titolo: Quale logica seguo per creare gli indici?

Vorrei capire in base a quali criteri creare degli indici sulle tabelle.
Presento un esempio.
----------------------------------------
Tabella ACCOUNT:
id		INT		PRIMARY KEY
username	VARCHAR(10)
id	username
--	--------
1	mario
2	luigi
3	user01
--------------------
Tabella AZIENDA:
id		INT		PRIMARY KEY
ragione_sociale	VARCHAR(40)
id	ragione_sociale
--	---------------
1	Rossi S.p.a.
2	Bianchi S.r.l.
--------------------
Tabella IMPOSTAZIONI:
id		INT		PRIMARY KEY
id_account	INT
id_azienda	INT
impostazione	VARCHAR(10)
valore		VARCHAR(10)
id	id_account	id_azienda	impostazione	valore
--	----------	----------	------------	------
1	0		0		colore		verde
2	3		0		colore		bianco
3	0		2		colore		rosso
----------------------------------------
Nella tabella IMPOSTAZIONI, il valore "0" in id_account significa 
"qualunque sia l'account" e il valore "0" in id_azienda significa 
"qualunque sia l'azienda".
Mi troverò a fare delle query del tipo:
SELECT valore FROM impostazioni WHERE impostazione='colore' AND 
(id_account=0 OR id_account=[un_certo_valore]) AND (id_azienda=0 OR 
id_azienda=[un_certo_valore]) ORDER BY id_account,id_azienda LIMIT 1
----------------------------------------
Ora da novellino mi chiedo: "Quali indici conviene creare sulla tabela 
IMPOSTAZIONI per rendere più veloci le query?".
Quale soluzione tra queste:
1) un indice sul campo id_account, uno su id_azienda e uno su impostazione;
2) un indice unico sui campi id_account e id_azienda;
3) entrambe le cose.
Secondo la mia logica e non conoscendo come lavorano i database, direi 
la soluzione 3) ovvero la 1) accelera la fase di elaborazione della 
WHERE e la 2) accelera l'ordinamento.
E' giusto?
Vorrei partire col piede giusto per non trovarmi con grossi problemi di 
prestazioni al crescere del volume dei dati.
Grazie per la pazienza.
Gabriele

 prestazioni al crescere del volume dei dati. Grazie per la pazienza.
Tranne che per gli indici con vincolo di unicita`, DBMS ti consente di 
aggiungere indici anche in seguito. Magari ci vorra` un po' a generarli se 
hai molti dati, ma una volta definiti li usa sempre.
Io, di solito, creo gli indici in base alla frequenza di utilizzo di un 
certo campo nella where delle query.
Poi devi trovare il giusto bilanciamento tra le richieste di velocita` e 
l'occupazione di spazio su disco (ogni indice occupa spazio aggiuntivo).
Non c'e` una regola che vale sempre.
Bye.

 Non c'e` una regola che vale sempre.
Sono d'accordo, ma volevo capire la logica di una query. Dunque: il db 
elabora la WHERE utilizzando gli indici se ci sono, dopodichè ordina i 
dati in base agli indici se ci sono. Giusto?
Scrivendo la riga precedente mi sorge il dubbio: se ho una WHERE del 
tipo "WHERE cognome LIKE %rossi%" questa NON si avvantaggia della 
presenza di un indice sul campo cognome, vero? Oppure no?
Grazie per l'attenzione
Gabriele

 campo cognome, vero? Oppure no?
Dipende dal tipo di indice e da cosa fornisce il DB.
Ti conviene cercare nella documentazione del tuo DB e vedere il capitolo 
sull'ottimizzazione. Di solito su usa EXPLAIN, DESCRIBE o qualcosa di 
simile per vedere quali indici vengono usati in una  query. Analizzando il 
risultato di questa operazione provi a creare gli indici e vedi come 
migliorano le prestazioni.
Bye.

 migliorano le prestazioni.
Grazie. Credo che questo sia un gran consiglio. Da newbie quale sono non 
ne conoscevo l'esistenza.
In effetti, da una immediata ricerca in google, ho capito che EXPLAIN 
"spiega" ciò che l'"ottimizzatore" ritiene di fare per eseguire la 
query. Questo mi chiarisce anche meglio l'importanza delle PREPARE. 
Inoltre posso immaginare che la questione "se gli indici velocizzano le 
order by" dipenda ancora dal lavoro dell'"ottimizzatore" 
("compilatore"?) del DB.
Grazie, ciao e buona serata.
Gabriele

 generarli se hai molti dati, ma una volta definiti li usa sempre.
Si possono aggiungere indici univoci anche in seguito, purche' i
valori eventualmente gia' presenti siano unici.
Non e' detto che il dbms usi un indice: il query optimizer puo'
scegliere di ignorare un indice quando ha un'alternativa migliore
(fosse anche una table scan) o quando un indice e' scarsamente utile
(es. indice poco selettivo), o semplicemente perche' non realizza
che puo' essergli utile (es. statistiche non aggiornate).
E' sorprendente quanto poco basta per rendere un indice inutile:
in caso di indice non unique, anche solo il 5% di valori duplicati
puo' far ignorare l'indice; il motivo e' che un lookup su tale indice
restituisce un numero X di blocchi che dovranno essere recuperati; se
i blocchi sono attorno al 25-30% (e in dipendenza da molti altri
fattori tra cui dimensione dei blocchi, presenza di indice
cluster, altre clausole nella Where...), l'indice verra' ignorato
in favore di una table scan.

 della presenza di un indice sul campo cognome, vero? Oppure no?
Dipende dalla query: ci puo' comunque essere un vantaggio
dall'esaminare solo il livello foglia dell'indice invece di una
table scan.
bye

The man with two watches ha scritto:

Da questa risposta e dall'altra tua, mi sembra di capire che la scelta 
degli indici sia piuttosto indeterminabile a priori e dipende molto da: 
dati, esperienza, motore di database.
Visto che non conosco i db, non ho esperienza in questo campo, non so 
prevedere esattamente i dati presenti nel db, come si procede 
normalmente? Si fa una stima di come funzioneranno le cose e la si 
verifica, periodicamente, utilizzando la EXPLAIN come mi hanno già 
consigliato?
Cos'è, sommariamente, uno schema EAV? Quello che vorrei fare è: per una 
certa impostazione dell'applicazione (ad esempio il colore) ho una 
impostazione generale con id_account=0 e id_azienda=0; se un account ha 
un colore specifico, sarà aggiunto un record con id_account=$valore_id e 
id_azienda=0 e così per un'azienda (l'applicazione deve essere 
multiaziendale).
Grazie
Gabriele

 Cos'è, sommariamente, uno schema EAV? [...]
Se fai una ricerca tramite Google Groups su questo ng, vedrai
che ne abbiamo parlato parecchio in passato.
Uno schema EAV e' un modo di disegnare lo schema, e non riguarda
l'uso o il significato dei dati (come invece descrivevi nel
seguito del tuo messaggio).
Siccome e' una mala pratica, e non avendo da parte tua (secondo
quello che ci dici) alcuna esperienza nel campo dei database, e'
preferibile imparare direttamente come si fanno le cose nel
modo "giusto" (il che e' in gran parte un percorso personale).
Ciao 

The man with two watches ha scritto:

Grazie a tutti.
In conclusione seguirò il buonsenso, farò pratica e mi formerò man mano.

  ...
E' importante capire di quale DBMS stai parlando perchè altrimenti si 
rischia di applicare erroneamente soluzioni generiche a dei casi 
specifici... con il risultato che si potrebberò peggiorare le prestazioni 
anzichè migliorarle.
Io sto affrontando la tua stessa problematica (non banale) su un database 
MySQL, e sarei particolarmente interessato ad approfondire il discorso 
dell'ottimizzazione delle query tramite l'utilizzo degli indici opportuni su 
questo newsgroup sperando di riuscire a consultarmi con qualche guru... la 
mia esperienza in tal senso non è certo di grosso rilievo anche se credo di 
avere raggiunto un discreto livello di competenza almeno a livello base.
Per adesso, quello che posso dirti è che nella documentazione di MySQL 
(disponibile online) troverai degli appositi capitoli dedicati 
all'argomento... se non erro credo che il capitolo 7 sia quello da cui 
partire... si imparano molte cose.
Spero che questa discussione abbia un seguito importante... magari se è il 
caso si potrebbe aprire un nuovo post.
Riccardo
-- 

 partire... si imparano molte cose.
Ci guarderò, grazie.
