lundi 17 octobre 2011

Indexation et booléens

Voilà encore une raison pour laquelle il faut toujours tester vos requêtes!
En effet, il s'avère que l'optimiseur de MySQL 5.5 (vérifié aussi en 5.1.60) ne fait pas le même choix selon qu'un filtre sur la valeur d'un booléen indexé utilise l'opérateur = ou IS. Voici un exemple extrait du rapport de Bug que j'ai posté sur bugs.mysql.com :

mysql> CREATE TABLE t(id INT, b BOOLEAN DEFAULT FALSE);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t(id) SELECT 1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 32 rows affected (0.00 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t;
Query OK, 64 rows affected (0.00 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 387 rows affected (0.00 sec)
Records: 387 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 1548 rows affected (0.02 sec)
Records: 1548 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 6192 rows affected (0.03 sec)
Records: 6192 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 24768 rows affected (0.18 sec)
Records: 24768 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t(id) SELECT id FROM t UNION ALL SELECT id FROM t UNION ALL SELECT id FROM t;
Query OK, 99072 rows affected (0.43 sec)
Records: 99072 Duplicates: 0 Warnings: 0
mysql> alter table t add index(b);
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t values(10,TRUE);
Query OK, 1 row affected (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE b IS TRUE\G
******** 1. row ********
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: b
key_len: 2
ref: NULL
rows: 131783
Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t WHERE b = TRUE\G
******** 1. row ********
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: b
key: b
key_len: 2
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)

Comme vous pouvez le voir, lorsque l'on utilise l'opérateur "=" , MySQL effectue un accès unique dans l'index b, alors qu'avec l'opérateur "IS" il effectue un parcours total de l'index... Il est donc très important pour l'instant de privilégier l'opérateur d'égalité en attendant la correction du bug.

Aucun commentaire: