ottimizzare un database | Progettazione di DataBase Relazionali

Innazitutto un complimento a questo forum ..è il mio primo post ..
E da un po di tempo che sto lavorando su mysql ed ora è giuto il momento dell'ottimizzazione dei miei database..

1) domanda .. meglio tabelle con tante colonne o con tante righe ?
Mi spiego meglio
faccio 2 esempi
1 )esempio
TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nome` varchar(255) NOT NULL,
`cognome` varchar(255) NOT NULL,
`via` varchar(255) NOT NULL,
.......
PRIMARY KEY (`id`)
)
2)esempio 2 Tabelle

TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_rif` int(11),
`id_type` int(11) varchar(255) NOT NULL,
`value` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
)

TABLE `tb_type` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`value` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
)
dove c'e una relazione tra tb_type.id=tb_user.id_type e id_rif è un campo dove ha lo stesso valore per dati apparteneti alla stessa persona

Il primo esempio è molto semplice ma ogni volta che devo aggiungere altre campi devo mettere mano alla struttura della tabella
il secondo è più versatile (cosi lavora ad esempio Magento) ma ho notato che estrapolare dati comporta un numero di join
tanti quanti sono i campi che voglio estrapolare
una select potrebbe essere :

SELECT DISTICT(id_rif), CONCAT (q0.value) AS Nome, Concat (q1.cognome) AS Cognome
FROM tb_user
LEFT JOIN (SELECT value FROM tb_user WHERE id_ref=1) AS q0 ON (q0.id_ref=tb_user.id_ref)
LEFT JOIN (SELECT value FROM tb_user WHERE id_ref=2) AS q1 ON (q1.id_ref=tb_user.id_ref)

Il Risultato del tipo

id | Nome | Cognome
1 Mario ROssi
2 Alberto Esposito

cioè una trasposizione (se così si può chiamare ) della tabella tb_user da verticale ad orizzontale

Onestamente la seconda modalità mi piace di più ma mi porta ad avere delle query che impiegano molto più tempo , anche mettendo indici ..
Cosa mi consigliate ?
Grazie

Come dici tu, il secondo metodo usa query che impiegano troppo tempo. Gli indici servono a poco, perchè il problema è il piano di esecuzione della query e non l'accesso ai record.

Un altro metodo è il cosiddetto modello ad ancora: consiste nel creare (almeno nei casi più estremi!) una tabella fisica per ogni attributo dell'entità. Ad esempio:

TABELLA: utente_nome
CHIAVE: id
CAMPO: nome

TABELLA: utente_cognome
CHIAVE: id
CAMPO: cognome

TABELLA: utente_indirizzo
CHIAVE: id
CAMPO: indirizzo

Le query ovviamente diventano molto complesse da scrivere, ma si rimedia usando una vista che riunisca gli attributi:

CREATE VIEW ... SELECT ... FROM utente_nome AS nome JOIN utente_cognome ON ... JOIN ....

Il problema è che quando interroghi quella vista, non sempre hai bisogno di tutti i campi. Ad esempio, se tu non leggi il nome, è inutile che MySQL acceda alla tabella utente_nome. Ma l'ottimizzatore di MySQL non è in grado di "eliminare" dal piano di esecuzione le tabelle di troppo (alcuni fork però lo fanno).

Io personalmente preferisco il primo approccio, quello classico.

Se ci sono molte colonne, con MyISAM ti consiglio di usare il formato FIXED (e quindi CHAR al posto di VARCHAR), in modo che lo storage engine abbia uno schema preciso per trovare tutti i dati di cui ha bisogno.

Se usi InnoDB, ti consiglio di fare tutte le operazioni di scrittura con le transazioni, usando il livello di isolamento READ-COMMITTED. Se hai molte tabelle con molte colonne, e queste contengono molti dati, dai a innodb_file_per_table un valore > 1. innodb_thread_concurrency=8 (minimo). Metti la chiave primaria fisicamente all'inizio della tabella, tienila il più breve possibile, e fai che tutti gli indici comprendano anche la chiave primaria.

Altro non mi viene in mente...