Comment créer un index sur la partie date du champ DATETIME dans MySql
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 de la transaction, comme il arrive
ma Table a plus de 1.000.000 d'enregistrements dans elle et la déclaration
SELECT * FROM transactionlist where date(TranDateTime) = '2008-08-17'
prend beaucoup de temps.
EDIT:
Regardez ce billet de blog sur " pourquoi le DATETIME de MySQL peut et doit être évitée "
13 réponses
si je me souviens bien, cela lancera un scan de table entier parce que vous passez la colonne à travers une fonction. MySQL exécutera obédiemment la fonction pour chaque colonne, en contournant l'index puisque l'optimiseur de requête ne peut pas vraiment connaître les résultats de la fonction.
Ce que je voudrais faire est quelque chose comme:
SELECT * FROM transactionlist
WHERE TranDateTime BETWEEN '2008-08-17 00:00:00' AND '2008-08-18 23:59:59';
Qui devrait vous donner tout ce qui est arrivé sur 2008-08-17, et tout ce qui s'est passé exactement 2008-08-18 00: 00: 00. Si c'est un problème, vous pouvez modifier le deuxième terme de "2008-08-17 23:59:59' et juste obtenir 2008-08-17.
Je ne veux pas paraître mignon, mais une façon simple serait d'ajouter une nouvelle colonne qui ne contenait que la partie date et l'index.
vous ne pouvez pas créer un index uniquement sur la partie date. Est-il une raison pour laquelle vous avez?
même si vous pouviez créer un index uniquement sur la 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'
Est efficace et fait ce que vous voulez.
une autre option (pertinente pour ver 7.5.3 et plus) est de créer une colonne générée/virtuelle basée sur la colonne datetime, puis de l'indexer.
CREATE TABLE `table` (
`my_datetime` datetime NOT NULL,
`my_date` varchar(12) GENERATED ALWAYS AS (DATE(`my_daetime`)) STORED,
KEY `my_idx` (`my_date`)
) ENGINE=InnoDB;
Je ne sais pas les spécificités de mySql, mais quel mal y a-t-il à indexer le champ date dans son intégralité?
alors il suffit de rechercher:
select * from translist
where TranDateTime > '2008-08-16 23:59:59'
and TranDateTime < '2008-08-18 00:00:00'
si les indices sont des arbres b ou quelque chose d'autre qui est raisonnable, ceux-ci devraient être trouvés rapidement.
Valeriy Kravchuk sur une demande de fonctionnalité pour ce même problème sur le site MySQL dit d'utiliser cette méthode.
" dans l'intervalle, vous pouvez utiliser des colonnes de caractères pour stocker des valeurs DATETIME comme chaînes, avec seulement les premiers N caractères étant indexés. Avec une utilisation prudente des déclencheurs dans MySQL 5, vous pouvez créer une solution raisonnablement robuste basée sur cette idée."
vous pourriez écrire une routine assez facile à ajouter cette colonne, et puis avec des déclencheurs garder cette colonne synchronisés. L'index sur cette colonne devrait être assez rapide.
la seule et bonne solution qui fonctionne assez bien est d'utiliser timestamp comme temps, plutôt que datetime. Il est stocké comme INT et être indexé assez bon. Personnellement j'ai rencontré un tel problème sur la table des transactions, qui a environ millions d'enregistrements et ralenti dur, enfin j'ai souligné que cela causé par mauvais champ indexé (datetime). Maintenant, il fonctionne très rapide.
datetime COMME quelque chose d'% ne sera pas attraper l'index.
utilisez ceci: où datetime_field >= cudate();
Qui va attraper l'indice,
et couvrir aujourd'hui:00:00:00 jusqu'à aujourd'hui:23:59:59
Faire.
que dit "expliquer"? (run EXPLAIN SELECT * FROM transactionlist where date (TranDateTime) = '2008-08-17')
S'il n'utilise pas votre index à cause de la fonction date (), une requête de portée devrait s'exécuter rapidement:
sélectionnez * dans la liste des transactions où TranDateTime > = '2008-08-17' et TranDateTime < '2008-08-18'
plutôt que de faire un index basé sur une fonction (si cela est même possible dans mysql) faites votre clause où faire une comparaison de gamme. Quelque chose comme:
Où TranDateTime > '2008-08-17 00:00:00', et TranDateTime < '2008-08-17 11:59:59')
cela permet à la DB d'utiliser L'index sur TranDateTime (il y en a un, non?) pour la sélectionner.
créez de nouveaux champs avec juste les dates convert(datetime, left(date_field,10)) et ensuite indexez-les.
Je ne sais pas les spécificités de mySQL, mais quel mal y a-t-il à indexer le champ date dans son intégralité?
Si vous utilisez fonctionnelle de magie pour les * * * les arbres, des hachages ... est parti, parce que pour obtenir des 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 voulez-vous dire quelque chose comme calculé (calculé?) index... mais à ce jour, Je ne l'ai vu qu'au Cachéintersystèmes. Je ne pense pas qu'il y ait un cas dans les bases de données relationnelles (AFAIK).
une bonne solution, à mon avis, est la suivante (exemple clintp mis à jour):
SELECT * FROM translist
WHERE TranDateTime >= '2008-08-17 00:00:00.0000'
AND TranDateTime < '2008-08-18 00:00:00.0000'
que vous utilisiez 00:00:00.0000 ou 00:00 à mon avis ne fait aucune différence (je l'ai généralement utilisé dans ce format).
pourquoi personne n'a suggéré D'utiliser comme ça? N'est-ce pas faire le travail aussi bien? Il sera aussi rapide qu'ENTRE les deux?
SELECT * FROM transactionlist where TranDateTime LIKE '2008-08-17%'