Comment créer un index sur la partie date du champ DATETIME dans MySql ?

Question

Comment créer un index sur la partie date du champ DATETIME ?

mysql> SHOW COLUMNS FROM transactionlist;
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| TransactionNumber | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| WagerId           | int(11)          | YES  | MUL | 0       |                |
| TranNum           | int(11)          | YES  | MUL | 0       |                |
| TranDateTime      | datetime         | NO   |     | NULL    |                |
| Amount            | double           | YES  |     | 0       |                |
| Action            | smallint(6)      | YES  |     | 0       |                |
| Uid               | int(11)          | YES  |     | 1       |                |
| AuthId            | int(11)          | YES  |     | 1       |                |
+-------------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

TranDateTime est utilisé pour enregistrer la date et l'heure d'une transaction au moment où elle se produit.

Ma table contient plus de 1 000 000 d'enregistrements et l'instruction

SELECT * FROM transactionlist where date(TranDateTime) = '2008-08-17' 

prend beaucoup de temps.

EDIT :

Jetez un coup d'œil à cet article de blog sur " Pourquoi le DATETIME de MySQL peut et doit être évité "

Answer 1

Si je me souviens bien, cela lancera un balayage complet de la table parce que vous faites passer la colonne par une fonction. MySQL exécutera docilement la fonction pour chaque colonne, en contournant l'index puisque l'optimiseur de requêtes ne peut pas vraiment connaître les résultats de la fonction.

Ce que je ferais, c'est quelque chose comme :

SELECT * FROM transactionlist 
WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-17 23:59:59.999999';

Cela devrait vous donner tout ce qui s'est passé le 17 août 2008.

Answer 2

Je ne veux pas paraître mièvre, mais un moyen simple serait d'ajouter une nouvelle colonne contenant uniquement la partie date et de l'indexer.

Answer 3

Vous ne pouvez pas créer un index sur la seule partie date. Y a-t-il une raison pour laquelle vous devez le faire ?

Même si vous pouviez créer un index sur la seule partie date, l'optimiseur ne l'utiliserait probablement pas pour la requête ci-dessus.

Je pense que vous trouverez que

SELECT * FROM transactionlist WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-18'

Il est efficace et fait ce que vous voulez.

Answer 4

Je ne connais pas les spécificités de mySql, mais quel est l'inconvénient de simplement indexer le champ date dans son intégralité ?

Alors, il suffit de chercher :

 select * from translist 
     where TranDateTime > '2008-08-16 23:59:59'
        and TranDateTime < '2008-08-18 00:00:00'

Si les index sont des b-trees ou autre chose de raisonnable, ils devraient être trouvés rapidement.

Answer 5

Valeriy Kravchuk, dans une demande de fonctionnalité pour ce même problème sur le site de MySQL, a indiqué qu'il fallait utiliser cette méthode.

"En attendant, vous pouvez utiliser des colonnes de caractères pour stocker des valeurs DATETIME sous forme de chaînes de caractères, seuls les N premiers caractères étant indexés. Avec une utilisation prudente des triggers dans MySQL 5, vous pouvez créer une solution raisonnablement robuste basée sur cette idée."

Vous pourriez écrire une routine assez facile pour ajouter cette colonne, et ensuite avec des triggers garder cette colonne synchronisée. L'index sur cette colonne de chaîne devrait être assez rapide.