Nella maggior parte di siti e blog, costruiti o meno su un CMS, i contenuti sono memorizzati in un database. Tutte le richieste-utente, pertanto, coinvolgono un DB che dovrà essere il più performante possibile al fine di velocizzare i tempi di risposta e fornire le informazioni richieste nel modo più veloce possibile.

Ci sono, senza dubbio, alcune accortezze e alcune modalità specifiche da mettere in pratica per realizzare quanto detto. Inoltre, sono diversi gli errori che si commettono sia nella progettazione del database e sia nelle varie interrogazioni fatte al db stesso tramite query.

In questo articolo volevo evidenziare alcune possibili strade da percorrere per realizzare un database performante con cui interagire in modo ottimale.

Progettazione

Si tratta del primo passo a tutti gli effetti : la progettazione e realizzazione del database. Questo caso non coinvolge applicazioni e siti che si appoggiano ad una piattaforma (come Wordpress, ad esempio) dove tutto il db è già costruito e strutturato.

Ma per tutti gli altri casi in cui occorre progettare da zero la struttura dati, vi sono regole ben precise da seguire e, soprattutto errori evidenti e deleteri da non commettere.

A tal riguardo, vi consiglio di leggere questo articolo che riguarda proprio come approcciare il design di un database.

Conoscere in dettaglio la Query

Per ottimizzare una specifica query, è fondamentale sapere in dettaglio cosa ci restituirà quella query al fine di discernere il superfluo dall’utile.

Utilizzando lo statement EXPLAIN otterrete molte utili informazioni sulla query eseguita. Ecco un esempio di codice sql :

EXPLAIN SELECT * FROM ref_table,other_table WHERE  ref_table.key_column=other_table.column;

Per maggiori info su come utilizzare Explain, leggete la guida ufficiale mysql al riguardo.

Query e Cache

Come sapete, ogni richiesta effettuata al db per ottenere dati si risolve in una richiesta di risorse al server che lo ospita. Questo significa che un sistema di cache queries, laddove possibile, porta effettivamente vantaggi in termini di prestazione. Vediamo tre possibili scelte :

• AdoDB : sta per Active Data Objects DataBase ed è una libreria che fornisce un’unica API per interfacciarsi ad uno dei DBMS supportati (MySQL, PostgreSQL, Interbase, Firebird, Informix, Oracle, MS SQL, Foxpro, Access, ADO, Sybase, FrontBase, DB2, SAP DB, SQLite, Netezza, LDAP. AdoDB fornisce, tra le altre cose, anche un potente caching system.
• Memcached : Memcached annovera un sistema di caching utilizzato laddove si interagisce con database molto carichi di dati per alleggerirne il compito
• CSQL Cache : è una infrastruttura open-source di data caching. Da quello che si legge al riguardo, sembra davvero molto affidabile.

Select Mirate

Si tratta, in pratica, di un errore comune, ma facilmente risolvibile. Una select del tipo :

SELECT * FROM wp_posts;

effettuata su una tabella wordpress (quella dei post, si capisce) ha senso solo se davvero vi occorrono tutti i dati di quella tabella. Ma se così non fosse, occorre filtrare a monte evitando di sprecare risorse inutili :

SELECT title, excerpt, author FROM wp_posts;

Questa potrebbe essere la select corretta qualora vi occorressero titolo,excerpt e autore dei post.

Usare LIMIT

Spesso, quello di cui abbiamo bisogno sono solo un numero limitato di records prelevati dal nostro database. Mi vengono in mente tutte quelle situazioni su Wordpress in cui, ad esempio, vogliamo solo mostrare ad esempio 10 post per pagina e simili. In tal caso, quindi, non ha senso estrarre tutti i record, ma utilizzando il parametro LIMIT possiamo selezionare solo il numero di records desiderato :

SELECT title, excerpt, author FROM wp_posts LIMIT 10;

Usare JOIN al posto delle sottoqueries

Troppo spesso si nota un abuso,da parte di alcuni programmatori, di subqueries. Potendo, l’ideale sarebbe non andarsi a complicare la vita con questo metodo, ma ricorrere al JOIN con cui ottenere, in modo più pulito, gli stessi identici risultati :

SELECT a.id,
    (SELECT MAX(created)
    FROM posts
    WHERE author_id = a.id)
AS latest_post FROM authors a

Questa select, sfruttado il JOIN, diventerebbe :

SELECT a.id, MAX(p.created) AS latest_post
FROM authors a
INNER JOIN  posts p
ON (a.id = p.author_id)
GROUP BY a.id

Usare UNION al posto di OR

Lo statement UNION consente di combinare il result sets di 2 o più select queries. A differenza dell’OR, permette esecuzioni nettamente più veloci.

SELECT * FROM a, b WHERE a.p = b.q or a.x = b.y;

diventa semplicemente :

SELECT * FROM a, b WHERE a.p = b.q
UNION
SELECT * FROM a, b WHERE a.x =  b.y

Usare Indici

Gli indici di un database sono simili a quelli di una libreria perché consentono di trovare le informazioni richieste in modo più rapido e, quindi, più performante.
Un indice può esser creato per una singola colonna o su più colonne in una tabella. Eccovi un esempio banale in cui si crea l’indice idModello sulla colonna Modello della Tabella Prodotto.

CREATE INDEX idModello ON Prodotto (Modello);

Fonte : Catswhocode