Ordinare per condizioni trovate

FN mi ha proposto un problema interessante. Deve ricercare un archivio usando tre condizioni unite dall’operatore OR, e gli serve che il risultato sia ordinato in modo che i record che rispondono alla prima condizione vengano per primi, poi quelli della seconda condizione, e infine quelli della terza.

Lui si è arenato alla semplice query senza ordinamento.


mysql> SELECT nome, stipendio, DDN
FROM impiegati
WHERE
nome LIKE 'M%'
OR DDN < '1960-01-01' OR stipendio > 6000;
+---------+-----------+------------+
| nome | stipendio | DDN |
+---------+-----------+------------+
| Mario | 5100 | 1956-10-24 |
| Marco | 5600 | 1943-03-09 |
| John | 5550 | 1955-04-02 |
| Maria | 5700 | 1979-12-11 |
| Colette | 6100 | 1960-08-14 |
| Antonio | 6200 | 1968-12-08 |
| Nina | 6100 | 1967-05-24 |
+---------+-----------+------------+
7 rows in set (0.00 sec)

Così a prima vista io ho tirato fuori una soluzione che sembra fare al caso suo. Basta ordinare con una funzione CASE che assegna un valore a ogni condizione.


mysql> SELECT nome, stipendio, DDN
FROM impiegati
WHERE nome like 'M%'
OR DDN < '1960-01-01' OR stipendio > 6000
ORDER BY
CASE
WHEN nome LIKE 'M%' THEN 1
WHEN DDN < '1960-01-01' THEN 2 WHEN stipendio > 6000 THEN 3
END;
+---------+-----------+------------+
| nome | stipendio | DDN |
+---------+-----------+------------+
| Mario | 5100 | 1956-10-24 |
| Marco | 5600 | 1943-03-09 |
| Maria | 5700 | 1979-12-11 |
| John | 5550 | 1955-04-02 |
| Colette | 6100 | 1960-08-14 |
| Antonio | 6200 | 1968-12-08 |
| Nina | 6100 | 1967-05-24 |
+---------+-----------+------------+
7 rows in set (0.01 sec)

Il brutto di questo approccio, mi fa gentilmente notare FN, è che le condizioni vengono valutate due volte, e anche se questo avviene solo per i record trovati (la clausola ORDER BY viene eseguita dopo che i record sono stati filtrati) è sempre uno spreco e un rallentamento.

Pertanto, concentrandomi sull’efficienza, ho trovato un’altra soluzione:


mysql> SELECT nome, stipendio, DDN
FROM impiegati
WHERE nome like 'M%'
UNION
SELECT nome, stipendio, DDN
FROM impiegati
WHERE DDN < '1960-01-01' UNION SELECT nome, stipendio, DDN FROM impiegati WHERE stipendio > 6000 ;

Questa query risolve il problema dell’inefficienza dell’operatore OR con campi diversi, e inoltre risolve anche il problema principale dell’ordinamento. Benché teoricamente l’ordine del risultato di una query sia imprevedibile, il modo in cui MySQL organizza i dati in una UNION fa sì che l’ordinamento sia quello voluto. Si ricordi che una UNION senza la clausola ALL elimina i record duplicati, per cui se un record viene ritrovato grazie alla prima condizione, verrà scartato quando sono valutate la seconda e la terza.
Risultato ottenuto, e ottimizzato allo stesso tempo!

L’indice fantasma

Forse non tutti sanno che, per ogni query, MySQL utilizza un solo indice per ogni tabella. Ciò significa che in certi casi il motore risponde più lentamente di quanto ci aspettiamo.

Per esempio, supponiamo di voler elencare tutti gli impiegati che appartengano a un dipartimento specifico o che lavorino in una data località. La ricerca solo per dipartimento (clausola WHERE nel campo id_dip) usa un indice, ed esegue il suo compito efficacemente.

Lo stesso accade se vogliamo cercare per località (indice id_loc). Ma quando proviamo a combinare le due ricerche, MySQL ci annuncia che non riesce a utilizzare nessun indice.


mysql> explain SELECT * FROM persone WHERE id_dip = 3 OR id_loc = 3;
+-------------+------+----------------+------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------+------+----------------+------+---------+------+------+-------------+
| persone | ALL | id_dip, id_loc | NULL | NULL | NULL | 2200 | Using where |
+-------------+------+----------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Se la nostra tabella ha milioni di record, questo tipo di ricerca significa avere un “full table scan”, cioè una lettura sequenziale di tutta la tabella. Il fatto è che quando MySQL analizza la query, tenta di trovare un indice che possa soddisfare entrambe le condizione in WHERE e, siccome gli indici possibili risolvono solo metà delle condizioni, costringendo a una lettura sequenziale per risolvere la seconda parte, il motore decide che è più conveniente abbandonare gli indici del tutto ed eseguire la lettura completa già in partenza.

Ma non ci scoraggiamo. Esiste una semplice soluzione che, tenendo conto della limitazione di un indice per ogni tabella, risolve l’impasse felicemente.


mysql> explain SELECT * FROM persone WHERE id_dip = 3
UNION
SELECT * FROM persone WHERE id_loc = 3;
+---------+------+---------------+---------+---------+-------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+------+---------------+---------+---------+-------+------+-------------+
| persone | ref | id_dip | id_dip | 4 | const | 134 | Using where |
| persone | ref | id_loc | id_loc | 4 | const | 21 | Using where |
+---------+------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

Usando una UNION, le query diventano due, consentendo a MySQL di usare un indice per ciascuna. Il risultato è che non viene più effettuata una lettura sequenziale della tabella, sostituita da due letture a indice. Per tabelle di grosse dimensioni il guadagno in termini di efficienza può talvolta misurarsi in minuti!