Commentaires

1. Le lundi 19 avril 2010 à 14:24, par Patrice Damezin

Je suis de plus en plus réservé par rapport à l'utilisation de timestamp suite aux problemes qu'il me fait rencontré avec le changement d'heure d'hiver à été : faire une requète où le champs timestamp est indexé avec une heure qui n'existe pas (genre le 28 mars 2010 à 2h30) l'empèche d'utiliser son index. Il se met alors à faire un fulltable scan ou utiliser un autre index pour filtrer la requète.

Il est très facile de générer une heure qui n'existe pas : Imaginons qu'on a un outils de stats qui tourne la nuit, lancé avant 2h, mais qui s'éternise au dela de 2h du matin et que l'on fait un where du genre :
"where dateheure > now() - interval 1 day".
si on est à J+1 après le changement d'heure, entre 2h et 3h du matin, bah l'index de dateheure va etre complètement ignoré.

Exemple :
-- je fais croire que je suis le lundi matin 28 mars 2010 à 3h30, 1/2 heure après le changement d'heure :
mysql> set timestamp = unix_timestamp('2010-03-28 03:30:00');
Query OK, 0 rows affected (0.00 sec)

-- il est 3h30
mysql> select now()\G
*************************** 1. row ***************************
now(): 2010-03-28 03:30:00
1 row in set (0.00 sec)

-- il y a une heure, NOW() - INTERVAL 1 HOUR me dit qu'il était 2h30... même si cette heure n'éxiste pas !
mysql> select now() - interval 1 hour\G
*************************** 1. row ***************************
now() - interval 1 hour: 2010-03-28 02:30:00
1 row in set (0.00 sec)

-- Une requète avec cette heure qui n'existe pas :
mysql> explain select * from log where dateheure > now() - interval 1 hour;

+----+-------------+-------+------+---------------+------+---------+------+-----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows      | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-----------+-------------+
|  1 | SIMPLE      | log   | ALL  | dateheure     | NULL | NULL    | NULL | 248371777 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-----------+-------------+
1 row in set, 2 warnings (0.00 sec)

l'index dateheure existe, mais il ne s'en sert pas. La raquète va quand même réussir à s'éxécuter. mais sans utiliser les index et en lisant largement plus de ligne que nécessaire. (aillllle mon serveur !)

En demandant une heure qui existe, il utilise parfaitement l'index.
mysql> explain select * from log where dateheure > now() - interval 2 hour;

+----+-------------+-------+-------+---------------+-----------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+-------------+
|  1 | SIMPLE      | log   | range | dateheure     | dateheure | 4       | NULL | 6663570 | Using where |
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Ce comportement n'existe qu'avec TIMESTAMP, car en effet il stock des unixtime et se conforme aux zones horaires pour restituer, alors que datetime est quant a lui bcp plus permissif et stock n'importe quoi, dont des heures qui n'existent pas dans ma timezone :
exemple :
mysql> create table toto (x datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into toto values (now() - interval 1 hour);
Query OK, 1 row affected (0.00 sec)

mysql> select * from toto;
+---------------------+
| x |
+---------------------+
| 2010-03-28 02:30:00 |
+---------------------+
1 row in set (0.00 sec)



Le plus embetant est de penser à ce genre de cas quand on dev sur des tables qui sont assez volumineuse.
Sinon il y a un workarround possible au moment de la requète, mais ce n'est pas beau à voir :

mysql> select from_unixtime(unix_timestamp(now() - interval 1 hour))\G
*************************** 1. row ***************************
from_unixtime(unix_timestamp(now() - interval 1 hour)): 2010-03-28 03:00:00
1 row in set (0.00 sec)

mysql> explain select * from log where dateheure > from_unixtime(unix_timestamp(now() - interval 1 hour));

+----+-------------+-------+-------+---------------+-----------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+-------------+
|  1 | SIMPLE      | log   | range | dateheure     | dateheure | 4       | NULL | 5606414 | Using where |
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+-------------+
1 row in set (0.00 sec)

(NB mon exemple sert aussi à montrer qu'il est mal d'avoir une "grosse" table de log qui se vide rarement... normalement je préfère faire des tables de logs journalière, mais certaines applis n'en n'ont malheureusement pas encore.)

Ajouter un commentaire