Comment créer un index sur la partie de la date de champ DATETIME dans MySql
Comment puis-je créer un index sur la partie de la date de 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 de la transaction, comme il arrive
Mon Tableau comporte plus de 1 000 000 enregistrements dans il et la déclaration
SELECT * FROM transactionlist where date(TranDateTime) = '2008-08-17'
prend beaucoup de temps.
EDIT:
Ont un coup d'oeil à cet article de blog sur "Pourquoi MySQL DATETIME peut et doit être évitée"
- avertissement commentaire pour le lien que vous avez suggéré un coup d'oeil: Le message est écrit avec une telle excitation et la rage qu'il a presque frontières sur le point de puérilité. Et l'écrivain n'est pas de battre en arrière toute critique , tout en mentionnant qu'il se tient derrière ce qu'il a dit , mais son point de chute élancée avec de chaque. Mais encore , n'est pas une perte de temps, si vous lisez les commentaires.
Vous devez vous connecter pour publier un commentaire.
Si je me souviens bien, ce qui sera un ensemble de l'analyse de la table parce que vous êtes de passage de la colonne à l'aide d'une fonction. MySQL va docilement l'exécution de la fonction pour chaque colonne, le contournement de l'indice depuis l'optimiseur de requête ne peut pas vraiment savoir les résultats de la fonction.
Ce que je ferais, c'est quelque chose comme:
Qui devrait vous donner tout ce qui est arrivé sur 2008-08-17.
DATETIME
; votre requête renvoie des résultats corrects et ne pas inclure les lignes avecTranDateTime=2008-08-18 00:00:00
.BETWEEN
plus rapide que l'utilisationwhere TranDateTime >= '2008-08-17' and TranDateTime < '2008-08-18'
?Je ne veux pas dire à son mignon, mais un moyen simple serait d'ajouter une nouvelle colonne qui ne contient que la partie de la date et de l'indice sur qui.
Vous ne pouvez pas créer un index sur la date de la partie. Est-il une raison pour laquelle vous avez?
Même si vous pouvez créer un index sur la partie de la date, l'optimiseur ne serait probablement pas encore l'utiliser pour la requête ci-dessus.
Je pense que vous trouverez que
Est efficace et fait ce que vous voulez.
Une autre option (pertinents pour la version 5.7.3 et ci-dessus) est de créer un générés/colonne virtuelle basée sur la colonne datetime, puis de les indexer.
Je ne connais pas les spécificités de mySql, mais où est le mal dans tout juste d'indexation le champ de date dans son intégralité?
Ensuite, il suffit de chercher:
Si les indices sont des b-arbres ou quelque chose d'autre qui est raisonnable, ceux-ci devraient être trouvé rapidement.
>= '2008-08-16' and ... < '2008-08-18'
. Le temps est supposé être00:00:00
.Valeriy Kravtchouk sur une demande de fonctionnalité pour cette question sur le site de MySQL, a déclaré à l'utilisation de cette méthode.
"En attendant, vous pouvez utiliser des colonnes de caractères pour stocker les valeurs DATETIME comme des chaînes de caractères, avec seulement les N premiers caractères de l'indexation. Avec une bonne utilisation de déclencheurs dans MySQL 5, vous pouvez créer assez solide solution basée sur cette idée."
Vous pouvez écrire une routine assez facile d'ajouter cette colonne, puis avec les déclencheurs garder cette colonne synchronisés. L'index sur cette colonne devrait être assez rapide.
La seule et bonne solution qui est assez bon travail est d'utiliser le timestamp comme le temps, plutôt que de datetime.
Elle est stockée sous forme de INT et de l'indexation assez bon.
Personnellement, j'ai rencontré ce genre de problème sur la table des transactions, qui a environ millions de disques et a ralenti dur, enfin je l'ai fait remarquer que cela est causé par le mauvais champ indexé (datetime).
Maintenant, il fonctionne très rapide.
Je ne connais pas les spécificités de mySQL, mais où est le mal dans tout juste d'indexation le champ de date dans son intégralité?
Si vous utilisez fonctionnelle de magie pour les * * * les arbres, hache, ... est parti, parce que pour l'obtention de valeurs, vous devez appeler la fonction. Mais, parce que vous ne connaissez pas les résultats à l'avance, vous aurez à effectuer un balayage complet de la table.
Il n'y a rien à ajouter.
Peut-être vous voulez dire quelque chose comme calculé (calculée?) indices... mais à date, je n'ai vu cela dans Intersystems Caché. Je ne pense pas qu'il y a un cas dans les bases de données relationnelles (autant que je sache).
Une bonne solution, à mon avis, est la suivante (mise à jour clintp exemple):
Si vous utilisez
00:00:00.0000
ou00:00
à mon avis ne fait aucune différence (j'ai utilisé généralement dans ce format).datetime COMME quelque chose d'% ne sera pas attraper l'index.
Utiliser ceci: OÙ datetime_field >= curdate();
Pour capter l'index,
et couvrir aujourd'hui:00:00:00 jusqu'à aujourd'hui:23:59:59
Fait.
Que signifie "expliquer" dire? (exécuter la commande explain SELECT * FROM transactionlist où la date(TranDateTime) = '2008-08-17')
Si ce n'est pas à l'aide de votre index en raison de la fonction date (), une gamme de requête doit courir vite:
SÉLECTIONNEZ * à PARTIR de transactionlist où TranDateTime >= '2008-08-17" ET TranDateTime < '2008-08-18'
Plutôt que de faire un index basé sur une fonction (si c'est même possible en mysql) faire votre clause where ne une gamme de comparaison. Quelque chose comme:
Ce qui permet à la DB utiliser l'index sur TranDateTime (il y en a un, non?) pour faire la sélection.
Si la modification de la table est une option, ou vous êtes en train de rédiger un nouveau, envisager de stocker la date et l'heure dans des colonnes distinctes avec chacun des types. Vous obtenez la performance en ayant une beaucoup plus petite clé de l'espace et de stockage réduite (par rapport à une date seule colonne dérivée à partir d'un datetime). Cela rend également possible l'utilisation de composés, les clés, même avant les autres colonnes.
À l'OP de cas:
Créer un nouveau champs avec juste les dates
convert(datetime, left(date_field,10))
et puis index.date(date_field)
?