Indici Composti e MySQL

Recentemente mi sono trovato ad ottimizzare SELECT con where su campi che facevano parte di un indice composto.

Premesso che l'ordine con cui è costruito un indice influenzerà le WHERE della select, riporto di seguito un esempio in cui l'uso dell'indice sarà ottimale.

Tralasciando la teoria sul funzionamento di un indice B-Tree e uno B+Tree e le loro differenze mi limiterò a riportare gli aspetti pratici della cosa.

Riporto sql della prova:

CREATE TABLE People (
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum('m', 'f')not null,
key(last_name, first_name, dob)
);

insert into People values('aaaaa','bbbbbbbbb','1967-03-01','m');
insert into People values('bbbbb','ffffff','1967-03-01','m');
insert into People values('ccccc','vvvvvv','1967-03-01','m');
insert into People values('ddddd','hhhhh','1967-03-01','m');
insert into People values('eeeee','sss','1967-03-01','m');
insert into People values('fffff','rrrr','1967-03-01','m');
insert into People values('ggggg','eeee','1967-03-01','m');


1) Cerco tutti campi dell'indice secondo l'ordine con cui ho creato l'indice 

mysql> explain  select * from People where last_name = 'ccccc' and first_name = 'vvvvvv' and dob = '1967-03-01';
+----+-------------+--------+------+---------------+-----------+---------+-------------------+------+-------------+
| id | select_type | table  | type | possible_keys | key       | key_len | ref               | rows | Extra       |
+----+-------------+--------+------+---------------+-----------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | People | ref  | last_name     | last_name | 107     | const,const,const |    1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------------------+------+-------------+
:> Indice usato OTTIMAMENTE
 
2) Cerco solo per last_name, cioè per la prima colonna dell'indice composto ricordo da key(last_name, first_name, dob)

mysql> explain  select * from People where last_name = 'ccccc' ;
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | People | ref  | last_name     | last_name | 52      | const |    1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
:> Indice usato OTTIMAMENTE
 
3) Cerco nel primo campo last_name solo 1 lettera

mysql> explain  select * from People where last_name like 'c%' ;
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | People | range | last_name     | last_name | 52      | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
:> Indice usato OTTIMAMENTE

 
4) Cerco sempre con la like c% ma questa volta sul secondo campo dell'indice cioè fisrt_name

explain  select * from People where first_name like 'c%' ;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | People | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+





:> Indice NON USATO
 
5) Cerco su last_name un range

 explain  select * from People where last_name between 'aaaaa' and 'bbbbb';
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | People | range | last_name     | last_name | 52      | NULL |    3 | Using where |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
:> Indice usato OTTIMAMENTE
 

6) Cerco il primo campo per intero e solo la prima parte del secondo

 explain select * from People where last_name = 'aaaaa' and first_name like 'b%';
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | People | range | last_name     | last_name | 104     | NULL |    2 | Using where |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
:> Indice usato OTTIMAMENTE


 7) Aggiungo un ORDER BY. Poiche l'indice è di per se ordinato, lo stesso  indice puo essere utilizzato per un ORDER BY ma  a condizione che l'ordine del SORT sia lo stesso ordine con cui è stato creato in precedenza l'indice.
  
explain select * from People where last_name = 'aaaaa' and first_name like 'b%' order by last_name;
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | People | range | last_name     | last_name | 104     | NULL |    2 | Using where |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+

:> Indice usato OTTIMAMENTE

Altresi : 

explain select * from People where last_name = 'aaaaa' and first_name like 'b%' order by dob;
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-----------------------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra                       |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | People | range | last_name     | last_name | 104     | NULL |    2 | Using where; Using filesort |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-----------------------------+

:> Indice NON USATO

Per capirci , non posso ordinare per il terzo campo "dob" se l'indice è stato creato ordinato per (last_name, first_name, dob).

Infatti non usa l'indice per il sort ma eseguo un nuovo sort , in memoria probabilmente vista cmq la scarsità di dati.

Alla prossima.
 
Ciaoaoaoaoa.