mysql: indici su tabelle grandi

[ZeroUno]

Salve.

Ho una tabella di grandi dimensioni, nell'ordine delle centinaia di MB e dei milioni di record.

la tabella ha 7 colonne, di cui una è la primary key (id autoincrement), una è il puntamento ad un'altra tabella (id) legata tramite relazione di integrità referenziale, una colonna data/ora, una colonna numerica (popolata ma non utilizzata) e le ultime due quelle che fanno volume. Sono di tipo varchar(255); al momento la prima contiene stringhe della lunghezza massima di 150 caratteri, mentre l'altro campo, sempre varchar(255) ha, al momento, una lunghezza massima di c.a.80 caratteri. Entrambi questi campi possono contenere duplicati.

La mia query fa una ricerca sul quest'ultimo campo, così l'ho indicizzato.

L'indice ha una dimensione di altissime, a mio parere:
record: 2859187
dati: 287 M
indice: 177 M

è normale una dimensione così alta?
ma soprattutto... di quanto mi si alzano le performance?

poi la cardinalità... non so precisamente quanto debba essere, ma mi equivale esattamente al numero di record. E' normale.

Ciao
01

[tgmx]

Sinceramente non ho mai guardato la dimensione degli indici ma posso dirti che in tabelle come la tua, prima e dopo aver indicizzato il campo su cui faccio interrogazioni i tempi si sono ridotti di 1/10 o forse più.
Tempo fa però avevo letto (forse sul sito mysql) che se possibile era preferibile indicizzare campi char e non varchar per incrementare le prestazioni e ridurre l'utilizzo di memoria... ma non ho mai verificato.

[ZeroUno]

Ora sono in fase di qualche cambio sul programma quindi l'applicativo non è usabile, ma appena funzionerà proverò a vedere che succede senza indice (in dimensione e velocità).
Per quanto riguarda il char sono daccordo che probabilmente accellera l'accesso ai dati, e forse riduce la dimensione degli indici, ma credo che aumenti in modo pauroso la dimensione della tabella. Io infatti ho molte (la maggioranza) stringhe corte, ma alcune sono lunghe.

Ciao
01

[ZeroUno]

Ho provato a togliere l'indice.
La tabella è passata da 550 M a 380M, ma effettivamente i tempi di ricerca sono saliti alle stelle, si, anche 10/1
la tabella ha 3 milioni di record.

adesso sto ricostruendo l'indice (considerando che la sola eliminazione ha portato 147sec, quanto ci metterà a fare la creazione? popolando la tabella e le altre correlate da zero impiego 15/20 min, ora dovrei metterci di meno)

dopo trasformo in char.

[ZeroUno]

Un indice è stato aggiunto in filename ( La query ha impiegato 1258.7071 sec )

Però io non ho capito la cardinalità:
La prima volta che creo la tabella è pari al numero di record. Man mano che tolgo record e ne metto di nuovi la cardinalità scende. Ora, dopo la ricostruzione dell'indice, è a c.a. il 50% nel numero dei record. n.b.: è plausibile che nella tabella ci siano il 50% di duplicati di quel campo (forse più che meno), ma se la cardinalità è bassa per questo.. perchè non lo è da subito?

Questi sono i dati statistici della tabella:

Spazio utilizzato Tipo Utilizzo

Dati 315,8 MiB
Indice 229,6 MiB
In eccesso 450,0 MiB
Effettivo 95,4 MiB
Totale 545,4 MiB

che significano "in eccesso" e "effettivo"?

Grazie
01

[ZeroUno]

con il char è improponibile.

A parte che ho fatto l'errore di optare per la conversione anzichè la creazione da zero (1h 45m di conversione), 900M di dati per 3.000.000 di record e 1G di indice, e alto peggiornamento dei tempi di ricerca.

Ciao
01