Como se cria um índice na parte de data do campo DATETIME no MySql

Como faço para criar um índice na parte de data do campo 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 é usado para salvar a data e a hora de uma transação quando isso acontece

Minha tabela tem mais de 1.000.000 de registros e a declaração

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

leva muito tempo.

EDITAR:

Dê uma olhada nesta postagem do blog sobre ” Por que o DATETIME do MySQL pode e deve ser evitado ”

Se bem me lembro, isso executará uma varredura de tabela inteira porque você está passando a coluna por uma function. O MySQL obedientemente executará a function para cada coluna, ignorando o índice, já que o otimizador de consulta não pode realmente conhecer os resultados da function.

O que eu faria é algo como:

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

Isso deve dar-lhe tudo o que aconteceu em 2008-08-17, e tudo o que aconteceu exatamente em 2008-08-18 00:00:00. Se isso é um problema, você pode mudar o segundo termo para ‘2008-08-17 23:59:59’ e ganhar 2008-08-17.

Não quero soar bonito, mas uma maneira simples seria adicionar uma nova coluna que contivesse apenas a parte da data e o índice nela.

Você não pode criar um índice apenas na parte da data. Existe uma razão que você tem que fazer?

Mesmo se você pudesse criar um índice apenas na parte da data, o otimizador provavelmente ainda não o usaria para a consulta acima.

Eu acho que você vai achar isso

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

É eficiente e faz o que você quer.

Outra opção (relevante para a versão 7.5.3 e posterior) é criar uma coluna virtual / gerada com base na coluna datetime e depois indexá-la.

 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; 

Eu não sei sobre as especificidades do mySql, mas qual é o dano em apenas indexar o campo de data em sua totalidade?

Então apenas pesquise:

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

Se os índices forem b-trees ou qualquer outra coisa que seja razoável, eles devem ser encontrados rapidamente.

Valeriy Kravchuk em um pedido de recurso para este mesmo problema no site MySQL disse para usar este método.

“Enquanto isso, você pode usar colunas de caracteres para armazenar valores de DATETIME como strings, com apenas os primeiros N caracteres sendo indexados. Com algum uso cuidadoso de triggers no MySQL 5 você pode criar uma solução razoavelmente robusta baseada nesta idéia.”

Você pode escrever uma rotina bem fácil de adicionar essa coluna e, em seguida, com os gatilhos, manter essa coluna sincronizada. O índice nessa coluna de string deve ser bem rápido.

A única e boa solução que é muito boa é usar timestamp como time, em vez de datetime. Ele é armazenado como INT e está sendo indexado suficientemente bem. Pessoalmente eu encontrei esse problema na tabela de transactions, que tem cerca de um milhão de registros e abrandou muito, finalmente, assinalei que isso causado por mau campo indexado (datetime). Agora corre muito rápido.

datetime LIKE something% também não irá capturar o índice.

Use isto: WHERE datetime_field> = curdate ();
Isso vai pegar o índice,
e cobrir hoje: 00: 00: 00 até hoje: 23: 59: 59
Feito.

O que ‘explicar’ diz? (execute EXPLAIN SELECT * FROM transactionlist onde date (TranDateTime) = ‘2008-08-17’)

Se não estiver usando o índice por causa da function date (), uma consulta de intervalo deve ser executada rapidamente:

SELECT * FROM lista de transactions onde TranDateTime> = ‘2008-08-17’ E TranDateTime <'2008-08-18'

Ao invés de fazer um índice baseado em uma function (se isso é possível no mysql) faça sua cláusula where fazer uma comparação de intervalo. Algo como:

Onde TranDateTime> ‘2008-08-17 00:00:00’ e TranDateTime <'2008-08-17 11:59:59')

Isso permite que o DB use o índice em TranDateTime (existe um, certo?) Para fazer o select.

Crie um novo campo apenas com as datas convert(datetime, left(date_field,10)) e depois indexe isso.

Eu não sei sobre as especificidades do mySQL, mas qual é o problema em apenas indexar o campo date em sua totalidade?

Se você usa magia funcional para * trees, hashes, … se foi, porque para obter valores você deve chamar a function. Mas, como você não sabe os resultados à frente, precisa fazer uma varredura completa da tabela.

Não há nada para adicionar.

Talvez você queira dizer algo como índices calculados (calculados?) … mas até agora, eu só vi isso no Intersystems Caché. Eu não acho que haja um caso em bancos de dados relacionais (AFAIK).

Uma boa solução, na minha opinião, é a seguinte (exemplo de clintp atualizado):

 SELECT * FROM translist WHERE TranDateTime >= '2008-08-17 00:00:00.0000' AND TranDateTime < '2008-08-18 00:00:00.0000' 

Se você usa 00:00:00.0000 ou 00:00 na minha opinião não faz diferença (eu geralmente usei neste formato).

Por que ninguém sugeriu usar o LIKE? Isso não faz o trabalho também? Será tão rápido quanto ENTRE?

 SELECT * FROM transactionlist where TranDateTime LIKE '2008-08-17%'