(Resolvido) Otimização de uma consulta

[Eduardo Lopes Couto]

Olá a todos.

Estou com um problema, eu tenho os seguintes dados em uma tabela...

|| *id* || *serial_id* || *operador_id* || *datahora* ||
|| 2511 || 5717 || 9 || 2011-05-20 07:39:21 ||
|| 2512 || 5718 || 8 || 2011-05-20 07:43:28 ||
|| 2513 || 5721 || 8 || 2011-05-20 07:54:50 ||
|| 2514 || 5720 || 9 || 2011-05-20 07:56:23 ||
|| 2515 || 5719 || 3 || 2011-05-20 07:59:25 ||
|| 2516 || 5722 || 1 || 2011-05-20 08:05:06 ||
|| 2517 || 5724 || 8 || 2011-05-20 08:08:25 ||
|| 2518 || 5723 || 9 || 2011-05-20 08:09:04 ||
|| 2519 || 5725 || 3 || 2011-05-20 08:16:49 ||
|| 2520 || 5726 || 1 || 2011-05-20 08:19:39 ||
|| 2521 || 5727 || 8 || 2011-05-20 08:19:51 ||
|| 2522 || 5728 || 9 || 2011-05-20 08:24:20 ||
|| 2523 || 5730 || 8 || 2011-05-20 08:29:39 ||
|| 2524 || 5729 || 3 || 2011-05-20 08:33:10 ||
|| 2525 || 5731 || 1 || 2011-05-20 08:38:29 ||
|| 2526 || 5732 || 9 || 2011-05-20 08:39:34 ||
|| 2527 || 5733 || 8 || 2011-05-20 08:39:45 ||
|| 2528 || 5734 || 3 || 2011-05-20 08:48:50 ||
|| 2529 || 5737 || 8 || 2011-05-20 08:51:32 ||
|| 2530 || 5736 || 9 || 2011-05-20 08:52:36 ||

Preciso retorna um resultado que me fale por hora qual a quantidade naquele horario, por exemplo...

07:30 - 50
08:30 - 100
09:30 - 20

e assim por diante. Criei a seguinte consulta....

select distinct CONCAT(HOUR(`mm`.`datahora`),':',30) as `datahora`
, (select count(1) from `maquina-montador` as `mm1`
                        where  convert(CONCAT(HOUR(`mm1`.`datahora`),MINUTE(`mm1`.`datahora`)), SIGNED ) 
                                    <= convert(CONCAT(HOUR(`mm`.`datahora`),30),SIGNED)
                        and convert(CONCAT(HOUR(`mm1`.`datahora`),MINUTE(`mm1`.`datahora`)) ,SIGNED) >= convert(CONCAT(HOUR(`mm`.`datahora`)-1,30),SIGNED)
                        and DAY(`mm1`.datahora) = DAY(`mm`.datahora)
                        and MONTH(`mm1`.datahora) =  MONTH(`mm`.datahora)
                        ) as `COUNT`
from `maquina-montador` as `mm`
where 1=1
and DAY(`mm`.datahora) = 20
and MONTH(`mm`.datahora) = MONTH(NOW()) 
group by `datahora`;

Consigo o resultado que quero, mas a consulta nos testes está demorando 1,4 segundos para uma tabela de 150 registros, meu problema é que essa consulta em produção será realizado em uma tabela com mais de 10000 registros.

Preciso otimizar essa consulta, no Oracle, se não me engano, para obter o mesmo resultado a função TRUNC resolve.

Existe uma funçao parecida no mysql???

Grato pela atenção de todos.

[Progr'amador]

sua tabela possue indices?

talvez seria apenas o caso de você criar indices pros campos que você faz a consulta.

Abs. Progr'amador

[Kakao]

Se não for necessário quebrar a hora nos 30 minutos fica muito fácil:

select date_format(datahora, '%H') as `datahora`, count(*) as 'TOTAL'
from `maquina-montador`
where DAY(datahora) = 20 and MONTH(datahora) = MONTH(NOW())
group by `datahora`;

Se a quebra for realmente necessária me fale que eu tento fazer.

[Eduardo Lopes Couto]

Grato pela atenção dos colegas...

Progr'amador, eu segui sua dica, criei um indice no campo datahora, com isso ganhei 0,5 segundos, mas ainda estava demorando 1,040 segundos a consulta.

Kakao, realmente tenho a necessidade de quebrar as horas da maneira que escrevi, isso é necessário pois esses dados são para mostrar o indice de produção de uma linha de montagem e o horário que a linha começa a produzir nunca é em horas cheias.

Mas fiz uma modificação na consulta, eu retirei as funções "concat" e "convert", agora o tempo de consulta é de 0,749 segundos em uma tabela com 2568 registros, obs, se eu retirar o index que criei esse tempo pula para 2,508 segundos, mais uma vez, obrigado pela dica Progr'amador.

select distinct CONCAT(HOUR(`mm`.`datahora`),':',30) as `datahora`
, (select count(1) from `maquina-montador` as `mm1`
                        where  
                                (
                                        (HOUR(`mm1`.`datahora`)=(HOUR(`mm`.`datahora`)-1) and MINUTE(`mm1`.`datahora`) > 30)
                                        
                                        or 
                                        
                                        (HOUR(`mm1`.`datahora`)=HOUR(`mm`.`datahora`) and MINUTE(`mm1`.`datahora`) <= 30)
                    
                                    )
                                and DAY(`mm1`.datahora) = DAY(`mm`.datahora)
                        and MONTH(`mm1`.datahora) =  MONTH(`mm`.datahora)
                        ) as `COUNT`
from `maquina-montador` as `mm`
where 1=1
and DAY(`mm`.datahora) = 20
and MONTH(`mm`.datahora) = MONTH(NOW())
group by `datahora`;

Caso alguém consiga uma consulta que baixe mais o tempo, poste para compartilhar....

Só tenho uma dúvida, será que esse tempo que consegui, 0,749s, vai aumetar muito quando os registros na tabela aumentarem? pois hoje essa consulta está restrita em uma linha de produção que tem um indice produtivo de mais ou menos 300 itens por dia, já em outras linhas de produção esse indice produtivo irá aumentar para 3 a 6 chegando algumas linhas a atingir 10000 mil itens por dia.

Grato pela atenção de todos.

[Kakao]

select 
    concat(case when minute(datahora) > 30 then hour(datahora) + 1 else hour(datahora) end, ':30') as `datahora`
    ,count(*) as 'COUNT'
from `maquina-montador`
where DAY(datahora) = 20 and MONTH(datahora) = MONTH(NOW())
group by `datahora`;

[Eduardo Lopes Couto]

Kakao.

Obrigado pela ajuda, essa consulta que enviou, só tive que corrigir o alias dado a coluna datahora, pois o group by está agrupando pela datahora da tabela e não pelo formatado, ficou assim:

select
    concat(case when minute(datahora) > 30 then hour(datahora) + 1 else hour(datahora) end, ':30') as `datahoraFormat`
    ,count(*) as 'COUNT'
from `maquina-montador`
where DAY(datahora) = 20 and MONTH(datahora) = MONTH(NOW())
 group by `datahoraFormat`
 order by `datahora`;

Com esse select o tempo foi de 0,344 sec para uma tabela com 164.352 registros e no segundo teste levou 3,323 sec para uma tabela com 1.614.816 registros.

Muito obrigado a todos pela ajuda.