[MySQL] Ottimizzazione del numero di campi

[emanueledg]

Ciao,
ho una tabella con circa 30 campi, e devo aggiungerne altri che esprimono informazioni di tipo booleano (si/no, attivato/disattivato etc).

Mi chiedevo: è più economico (e performante) avere un unico campo INT dedicato a salvare questi flag con una logica binaria, oppure creare tanti campi quanti sono i flag, con valore booleano 0/1 (TINYINT oppure ENUM con 2 opzioni)?

[daniele_dll]

beh, avere un sacco di campi INT non è il massimo (anche perché un ENUM con 2 valori è molto più efficente sia a livello di gestione sia a livello di spazio su disco [anche se questo, nell'immediato, non è un problema])

dai un occhiata al tipo di campo SET
http://dev.mysql.com/tech-resources/...-datatype.html

con 8 byte (lo spazio occupato da 2 int) puoi gestire fino a 64 valori booleani

PS: sposto in database dato che è la sezione corretta per parlare di queste cose

[emanueledg]

Originariamente inviato da daniele_dll
beh, avere un sacco di campi INT non è il massimo (anche perché un ENUM con 2 valori è molto più efficente sia a livello di gestione sia a livello di spazio su disco [anche se questo, nell'immediato, non è un problema])

dai un occhiata al tipo di campo SET
http://dev.mysql.com/tech-resources/...-datatype.html

con 8 byte (lo spazio occupato da 2 int) puoi gestire fino a 64 valori booleani

PS: sposto in database dato che è la sezione corretta per parlare di queste cose

Grazie Daniele,
ho letto quell'articolo ed è interessante, avevo appreso del datatype SET molto tempo fa ma non ho mai avuto finora la necessità di utilizzarlo e lo avevo un po' tralasciato, tra le opzioni possibili.

Mi sembra di aver capito che dovendo estrarre dei campi in base ai sotto-valori dei campi SET attraverso operazioni bit a bit sia oneroso, in quanto l'operazione deve prima essere effettuata su tutta la tabella e quindi valutati i risultati derivanti.

Ci ho ragionato un po', e nel mio caso devo poter filtrare le query di ricerca anche per alcuni di quei campi, quindi magari userò il campo SET solo per quelli su cui non è necessaria la ricerca.

Sugli altri campi penso che userò un enum('0', '1'), da quanto mi dici sembrano più leggeri in termini di spazio della tabella e anche più performanti rispetto ad un INT(1).

[daniele_dll]

Originariamente inviato da emanueledg
Ci ho ragionato un po', e nel mio caso devo poter filtrare le query di ricerca anche per alcuni di quei campi, quindi magari userò il campo SET solo per quelli su cui non è necessaria la ricerca.

Sugli altri campi penso che userò un enum('0', '1'), da quanto mi dici sembrano più leggeri in termini di spazio della tabella e anche più performanti rispetto ad un INT(1).

Ciao,

non credo che le ricerche sui campi SET siano onerose, benché non abbia mai fatto alcun test, ma provo ad andarci per logica: un campo SET non è altro che un insieme di BIT settati a 1 o a 0.

Ora, in C, per leggere un bit, basta fare un byte shifting ed applicare una maschera booleana (entrambe operazioni INFINITAMENTE veloci, molto più di quanto possa immaginare) ovvero per leggere il 3 bit si dovrebbe usare qualcosa tipo:
bit = (value >> 2) & 0x01

Il che significa che se nella ricerca vuoi effettuare vuoi utilizzare un campo set mysql effettuerà quest'operazione N volte, 1 per bit da verificare (anche se probabilmente l'algoritmo di ricerca userà un sistema più avanzato per ridurre le operazioni).

Ora, non so che tipo di query devi effettuare, ma se al posto dei campi set vuoi usare dei campi enum stai sicuro che le query saranno più lente, ed anche per un motivo abbastanza semplice: se devo verificare 3 bit in un valore (soprattutto se si tratta solo di AND) posso farlo in un'unico colpo mentre se devo verificare 3 valori distinti dovrò fare 3 operazioni distinte.

Però ti ripeto, tutto questo per logica perché non ho effettuato alcun tipo di test sulle performance del tipo di campo

[emanueledg]

Hai ragione, rileggendo bene la parte dell'articolo che valuta le performance l'autore dice:

Because of the functions being performed on the rows, this type of query cannot benefit from an index. This means that the entire table will have to be scanned to find matches to user 8. However, as this is a bitwise operation, it should be quite efficient in spite of the full table scan (I was able to perform this operation on a randomly generated table of 500,000 rows and have 250,000 rows returned in 1.89 seconds on my 1.6 Ghz PC).

In pratica l'operazione di ricerca su campi set non può beneficiare di indici, ma ciononostante è molto veloce (credo per la velocità delle operazioni bitmask di cui parlavi tu) tanto che su una tabella random di 500000 righe in meno di 2 secondi ha tirato fuori la metà di campi con una WHERE CLAUSE che valutava i valori SET.

Ok, userò questo data type!
Grazie!

[daniele_dll]

sarebbe interessante fare qualche test pratico comunque, nel fine settimana vedo di fare qualche prova mettendo su 2 tabelle, entrambe con una chiave primaria numerica e poi con 8 campi si/no (nella prima tabella un campo set nella seconda 8 campi enum di cui 3 con indice)

[optime]

il tipo di campo SET è solo di MySql? io considererei anche la portabilità...

[daniele_dll]

beh, diciamo che altri database hanno altre varianti nel senso che ad esempio mssql (da quanto ho potuto trovare su google) ti fa effettuare le operazioni di byte shifting e permette di applicare maschere booleane

comunque, in generale, la portabilità di un'applicativo, se non previsto fin dall'inizio, significa rimettere mano a quasi tutte le query e alla struttura delle tabelle quindi l'usare un campo di tipo set può essere un problema se lui, fin dall'inizio, ha previsto di far funzionare il sistema su backend dati diversi o meno ^^