Indicizzazione MySQL: da ricerche di 2 secondi a risultati in 30 ms

Un indice appropriato può trasformare una ricerca prodotti di 2 secondi in 30 ms – un miglioramento del 98%. Eppure la maggior parte dei siti WordPress utilizza solo indici predefiniti e molte applicazioni PHP personalizzate vengono costruite senza alcuna strategia di indicizzazione. Questa guida ti insegna a progettare indici che faranno urlare il tuo database.

Perché l’indicizzazione è importante per le prestazioni web

Quando un utente visita una pagina che interroga un database (elenchi di prodotti, risultati di ricerca, profili utente), MySQL deve trovare le righe pertinenti. Senza indici, MySQL esegue una scansione completa della tabella – leggendo ogni riga fino a trovare corrispondenze. Per una tabella con 100.000 righe, una scansione completa richiede 200‑500 ms. Con 1.000 utenti contemporanei, si traducono in secondi di ritardo e la CPU del server si satura.

Gli indici sono come l’indice di un libro: dicono a MySQL esattamente dove trovare le righe, trasformando un’operazione O(n) in O(log n). Il risultato sono query che restituiscono risultati in millisecondi invece che in secondi.

Come funzionano gli indici MySQL (in termini semplici)

MySQL utilizza indici B‑Tree per impostazione predefinita. Memorizzano i valori delle colonne in una struttura ad albero ordinata. Quando esegui una query con una clausola WHERE su una colonna indicizzata, MySQL attraversa l’albero per trovare i valori corrispondenti in circa log₂(N) passaggi. Per 1 milione di righe, si tratta di circa 20 passaggi invece di 1 milione di scansioni.

Regola critica: Gli indici aiutano quando filtri per uguaglianza (=), intervallo (<, >, BETWEEN) o corrispondenza di prefisso (LIKE 'termine%'). Non possono aiutare con LIKE '%termine%' (carattere jolly iniziale) o chiamate di funzione come WHERE DATE(created_at) = '2025-01-01'.

Identificare le query lente – utilizzo di EXPLAIN

Prima di aggiungere indici, trova le tue query più lente. Abilita il log delle query lente di MySQL:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;  -- log queries slower than 0.5 seconds
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Quindi esegui mysqldumpslow -s t /var/log/mysql/slow.log per vedere le principali query lente.

Successivamente, anteponi EXPLAIN a qualsiasi query sospetta per vedere come MySQL la esegue. Ad esempio:

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price < 100;

Guarda la colonna type:

• ALL – scansione completa della tabella (peggiore).
• range o ref – utilizzo di un indice (buono).
• const – ricerca per chiave primaria (migliore).

Controlla anche rows – il numero di righe scandite. Se è vicino al totale della tabella, aggiungi un indice.

Strategia pratica di indicizzazione – indici singoli vs compositi

Indici a colonna singola

Usali quando filtri frequentemente su una colonna.

CREATE INDEX idx_user_id ON orders (user_id);

Indici compositi (multi‑colonna)

Quando filtri su più colonne, un indice composito è molto più efficiente di indici separati. MySQL può utilizzare un solo indice per riferimento di tabella, ma un indice composito copre più colonne.

-- Slow: 2 seconds, scans 50,000 rows
SELECT * FROM products WHERE category_id = 5 AND price < 100;

-- Add composite index (order matters!)
CREATE INDEX idx_category_price ON products (category_id, price);

-- Now: 30ms, scans 127 rows

Regola dell’ordine: Metti prima la colonna più selettiva (quella che filtra il maggior numero di righe). Di solito category_id riduce 50.000 → 5.000, poi price riduce a 200. Se inverti (price, category_id), l’indice sarebbe meno efficiente per le query di intervallo sul prezzo.

Indici di copertura (il Santo Graal)

Se la tua query necessita solo di colonne presenti nell’indice, MySQL può rispondere alla query interamente dall’indice – senza bisogno di toccare i dati della tabella. Questo è estremamente veloce.

-- Query only needs id and name
SELECT id, name FROM products WHERE category_id = 5;

-- Covering index
CREATE INDEX idx_category_id_name ON products (category_id, name);

-- EXPLAIN will show "Using index" in Extra column

Esempio reale: filtro prodotti ecommerce – prima e dopo

Un negozio online con 50.000 prodotti aveva una pagina di ricerca che filtrava per categoria, marca e fascia di prezzo. La query originale:

SELECT * FROM products 
WHERE category_id = 12 
  AND brand_id IN (3,7,9) 
  AND price BETWEEN 50 AND 200 
ORDER BY price LIMIT 24;

Senza indice: scansione completa della tabella – 2,3 secondi, 50.000 righe scandite.

Dopo l’analisi: Hanno aggiunto un indice composito:

CREATE INDEX idx_category_brand_price ON products (category_id, brand_id, price);

Risultato: La stessa query è stata eseguita in 80 ms (miglioramento del 96%), scandendo solo 312 righe. Il tempo di caricamento della pagina è sceso da 3,2 secondi a 0,6 secondi.

Indicizzazione per WordPress (se devi restare)

WordPress è notoriamente famoso per le query lente a causa del suo schema generico. La tabella wp_postmeta memorizza ogni campo personalizzato come coppia chiave‑valore, portando a milioni di righe. Puoi aggiungere indici personalizzati per migliorare le query comuni:

-- If you frequently query postmeta by meta_key and meta_value
ALTER TABLE wp_postmeta ADD INDEX idx_meta_key_value (meta_key, meta_value(100));

Ma anche con l’indicizzazione, WordPress soffre ancora del sovraccarico di wp_query. Le applicazioni PHP personalizzate progettate con uno schema e indici appropriati saranno sempre più veloci.

Manutenzione degli indici – quando aggiungere, quando rimuovere

• Aggiungi indici sulle colonne utilizzate in WHERE, JOIN, ORDER BY e GROUP BY.
• Rimuovi indici su colonne usate raramente o con cardinalità molto bassa (es. una colonna `status` con solo due valori). Ogni indice rallenta INSERT, UPDATE e DELETE perché MySQL deve aggiornare anche l’indice.
• Monitora l’utilizzo degli indici con SHOW INDEX FROM table; e SELECT * FROM sys.schema_unused_indexes; (MySQL 8.0).

Strumenti per l’analisi dell’indicizzazione

• EXPLAIN – integrato, inizia sempre da qui.
• MySQL Workbench – EXPLAIN visivo e dashboard delle prestazioni.
• pt‑query‑digest (Percona Toolkit) – analizza il log delle query lente e suggerisce indici.
• phpMyAdmin – funzione “Profiling” per vedere i dettagli di esecuzione delle query.

Caso di studio cliente: piatta immobiliare

Un sito immobiliare aveva 200.000 annunci di proprietà. Gli utenti cercavano per città, fascia di prezzo, camere da letto e tipo di proprietà. La pagina di ricerca impiegava 4–6 secondi per caricarsi perché la query scandiva l’intera tabella ogni volta.

Soluzione: Dopo aver analizzato i log lenti e utilizzato EXPLAIN, abbiamo aggiunto un indice composito:

CREATE INDEX idx_city_price_beds_type ON properties (city_id, price, bedrooms, property_type);

Abbiamo anche modificato la query per utilizzare un indice di copertura selezionando solo le colonne necessarie (id, titolo, prezzo, miniatura) invece di `SELECT *`.

Risultati:

• Tempo di query: 5,2 secondi → 90 ms.
• Carico della CPU del server ridotto del 70%.
• Tempo di caricamento della pagina: 6,5 s → 1,2 s (frontend incluso).
• Il coinvolgimento degli utenti è aumentato del 34% perché gli utenti non abbandonavano le ricerche lente.

Errori comuni e come risolverli

Errore 1: Indicizzare ogni colonna

Problema: Troppi indici rallentano le scritture. Soluzione: Indica solo le colonne che compaiono in WHERE, JOIN o ORDER BY.

Errore 2: Usare funzioni su colonne indicizzate

Sbagliato: WHERE DATE(created_at) = '2025-01-01' – ignora l’indice su created_at. Giusto: WHERE created_at BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 23:59:59'.

Errore 3: Carattere jolly iniziale in LIKE

Sbagliato: WHERE name LIKE '%widget%' – non usa mai l’indice. Giusto: Usa un indice di ricerca full‑text per corrispondenze parziali.

Errore 4: Non usare EXPLAIN prima e dopo

Esegui sempre EXPLAIN prima di aggiungere un indice, e di nuovo dopo, per verificare il miglioramento.

Avanzato: Automatizzare i suggerimenti di indici

Per MySQL 8.0, puoi abilitare il performance schema e utilizzare lo schema sys per trovare gli indici mancanti:

SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

Per le versioni precedenti, pt-index-usage di percona-toolkit analizza i log lenti e suggerisce indici.

Pronto a ottimizzare il tuo database?

Costruisco applicazioni PHP personalizzate con una corretta indicizzazione del database fin dal primo giorno. Che tu abbia un sito WordPress lento o un’app personalizzata con colli di bottiglia nelle query, posso analizzare i tuoi log lenti, aggiungere gli indici giusti e spesso ridurre i tempi di caricamento delle pagina del 50% o più.

Parliamo dei tuoi problemi di prestazioni del database. Fornirò una valutazione gratuita delle tue query più lente.

Tutti i dati sulle prestazioni provengono da audit reali di clienti che utilizzano MySQL 8.0 su VPS Hostinger. I tuoi risultati possono variare in base alle dimensioni delle tabelle e all’hardware del server.