Tab_1
cpo_a varchar(20) default null,
cpo_b int default 0,
cpo_c datetime default null,
cpo_d char(5) default null
Index indx01 (cpo_a, cpo_b, cpo_c);
Para esta tabela, usando o índice são válidas as seguintes pesquisas, para utilização do índice:
- cpo_a, cpo_b, cpo_c
- cpo_a, cpo_b
- cpo_a
Caso o cpo_a NÃO seja utilizado em uma pesquisa, o índice NÃO será usado, como nos exemplos abaixo:
- cpo_b, cpo_c
- cpo_b
- cpo_c
Outros exemplos de pesquisa, aonde o índice não será usado:
- cpo_a, cpo_c (falta o cpo_b)
- cpo_c, cpo_a (falta o cpo_b)
mysql> EXPLAIN SELECT * from tabela_a WHERE cd_reg = 1;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | tabela_a | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * from tabela_a WHERE cpo_a = 'a';
+----+-------------+----------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+--------+---------+-------+------+-------------+
| 1 | SIMPLE | tabela_a | ref | indx01 | indx01 | 10 | const | 1 | Using where |
+----+-------------+----------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * from tabela_a WHERE cpo_a = 'd';
+----+-------------+----------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+--------+---------+-------+------+-------------+
| 1 | SIMPLE | tabela_a | ref | indx01 | indx01 | 10 | const | 1 | Using where |
+----+-------------+----------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * from tabela_a WHERE cpo_a = 'd' and cpo_b = '2016-03-10' and cpo_c = 3;
+----+-------------+----------+------+---------------+--------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+--------+---------+-------------------+------+-------------+
| 1 | SIMPLE | tabela_a | ref | indx01 | indx01 | 24 | const,const,const | 1 | Using where |
+----+-------------+----------+------+---------------+--------+---------+-------------------+------+-------------+
1 row in set, 2 warnings (0.01 sec)
mysql> EXPLAIN SELECT * from tabela_a WHERE cpo_a = 'd' and cpo_b = '2016-03-10';
+----+-------------+----------+------+---------------+--------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+--------+---------+-------------+------+-------------+
| 1 | SIMPLE | tabela_a | ref | indx01 | indx01 | 15 | const,const | 1 | Using where |
+----+-------------+----------+------+---------------+--------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * from tabela_a WHERE cpo_a = 'd';
+----+-------------+----------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+--------+---------+-------+------+-------------+
| 1 | SIMPLE | tabela_a | ref | indx01 | indx01 | 10 | const | 1 | Using where |
+----+-------------+----------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * from tabela_a WHERE cpo_a = 'd' and cpo_c = '2016-03-10' and cpo_b = 3;
+----+-------------+----------+------+---------------+--------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+--------+---------+-------------------+------+-------------+
| 1 | SIMPLE | tabela_a | ref | indx01 | indx01 | 24 | const,const,const | 1 | Using where |
+----+-------------+----------+------+---------------+--------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * from tabela_a WHERE cpo_c = '2016-03-10' and cpo_b = 3;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tabela_a | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * from tabela_a WHERE cpo_b = 3;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tabela_a | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
