(Resolvido) Consulta com calculo de diferentes horas

[Eduardo Assis]

Tenho uma consulta no Mysql com php da seguinte maneira.

Uma tabela registra a entrada e saida, tendo os campos time, o que tenho

que fazer é na mesma tabela procurar pela entrada, e verificando que horas foi a saida.

Tendo $horainicio e $horafim, estou convertendo as horas para segundos, e depois subtraindo $horainicio por $horafim

tendo a diferença e convertendo novamente para formato em horas.

Tudo funciona, mas está demorando muito, pois ele procura por uns 8Mil registros é um select dentro de outro select.

Teriam alguma ideia de melhorar essa consulta ?

Segue parte do codigo que tenho hj.

$sql="SELECT time,callid,agent FROM queue_log where $data and (event = 'COMPLETECALLER' or event = 'COMPLETEAGENT')";

$query=mysql_query($sql,$conexao);

$atendidas = 0;

while ($registro=mysql_fetch_array($query))

{

$sql1="SELECT time,callid,arg2 FROM queue_log where $data and callid = '". $registro["callid"] . "' and event=ENTERQUEUE'";

$query1=mysql_query($sql1,$conexao);

$registro1=mysql_fetch_array($query1);

if (!empty($registro1[0]))

{

$horasini = explode(" ", $registro1["time]);

$horasfim = explode(" ", $registro["time"]);

$tempoini = explode(":",$horasini[1]);

$horaini = $tempoini[0];

$minutoini = $tempoini[1];

$segundoini = $tempoini[2];

$tempofim = explode(":",$horasfim[1]);

$horafim = $tempofim[0];

$minutofim = $tempofim[1];

$segundofim = $tempofim[2];

$convhoraini = ($horaini * 60) * 60;

$convminutoini = $minutoini * 60;

$convsegundoini = $segundoini;

$totalconvini = $convhoraini + $convminutoini + $convsegundoini;

$convhorafim = ($horafim * 60) * 60;

$convminutofim = $minutofim * 60;

$convsegundofim = $segundofim;

$totalconvfim = $convhorafim + $convminutofim + $convsegundofim;

$totalconv = $totalconvfim - $totalconvini;

$tempo = $tempo + $totalconv;

if ($maiortempo < $totalconv)

$maiortempo = $totalconv;

if (empty($menortempo))

$menortempo = $maiortempo;

if (($menortempo > $totalconv) && ($totalconv > 0))

$menortempo = $totalconv;

$atendidas++;

}

[Denis Courcy]

Oi, 'Eduardo Assis'

Por favor, poste a estruturas das tabelas para que possamos orientá-lo em como corrigir o problema. Seu problema está na falta ou no mau uso de índices.

[Eduardo Assis]

Estrutura da tabela:

CREATE TABLE IF NOT EXISTS `queue_log` (

`id` int(11) NOT NULL auto_increment,

`time` datetime NOT NULL default '0000-00-00 00:00:00',

`callid` varchar(20) NOT NULL default '',

`queuename` varchar(20) NOT NULL default '',

`agent` varchar(20) NOT NULL default '',

`event` varchar(20) NOT NULL default '',

`arg1` varchar(100) NOT NULL default '',

`arg2` varchar(100) NOT NULL default '',

`arg3` varchar(100) NOT NULL default '',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=106389 ;

Alguns Dados:

id time callid queuename agent event arg1 arg2 arg3

226 2008-11-26 17:19:33 1227727113.42 atendimento DGV/44 CONNECT 60 1227727158.47

227 2008-11-26 17:19:33 1227727104.40 atendimento DGV/38 COMPLETECALLER 61 8 2

228 2008-11-26 17:19:39 1227727113.42 atendimento DGV/44 COMPLETECALLER 60 6 4

229 2008-11-26 17:19:42 1227727146.45 atendimento DGV/38 CONNECT 36 1227727177.49

230 2008-11-26 17:19:47 1227727169.48 atendimento DGV/44 CONNECT 18 1227727183.50

231 2008-11-26 17:19:48 1227727137.44 atendimento NONE ABANDON 1 5 51

232 2008-11-26 17:19:53 1227727193.51 atendimento NONE ENTERQUEUE 1934651339

233 2008-11-26 17:19:53 1227727169.48 atendimento DGV/44 COMPLETECALLER 18 6 4

234 2008-11-26 17:19:54 1227727146.45 atendimento DGV/38 COMPLETECALLER 36 12 6

235 2008-11-26 17:20:00 1227727200.53 atendimento NONE ENTERQUEUE 1934618470

236 2008-11-26 17:20:09 1227727200.53 atendimento DGV/44 CONNECT 9 1227727200.54

237 2008-11-26 17:20:13 1227727213.55 atendimento NONE ENTERQUEUE 1936014000

238 2008-11-26 17:20:23 1227727193.51 atendimento DGV/38 CONNECT 30 1227727198.52

239 2008-11-26 17:20:24 1227727213.55 atendimento NONE ABANDON 1 2 11

240 2008-11-26 17:20:24 1227727224.56 atendimento NONE ENTERQUEUE 1930124000

241 2008-11-26 17:20:29 1227727229.57 atendimento NONE ENTERQUEUE 1930120582

242 2008-11-26 17:20:32 1227727193.51 atendimento DGV/38 COMPLETECALLER 30 9 1

[Denis Courcy]

Oi, 'Eduardo Assis'

Conforme eu suspeitava o problema é a falta de índice. Esta falta de índice está provocando um "TABLE SCAN" que é uma varredura em todos os registros da tbela.

A estrutura da tabela, conforme você passou é:

CREATE TABLE IF NOT EXISTS `queue_log` (
  `id` int(11) NOT NULL auto_increment,
  `time` datetime NOT NULL default '0000-00-00 00:00:00',
  `callid` varchar(20) NOT NULL default '',
  `queuename` varchar(20) NOT NULL default '',
  `agent` varchar(20) NOT NULL default '',
  `event` varchar(20) NOT NULL default '',
  `arg1` varchar(100) NOT NULL default '',
  `arg2` varchar(100) NOT NULL default '',
  `arg3` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=106389;

Rode o script abaixo para a inclusão deste índice.

ALTER TABLE queue_log 
ADD INDEX queue_log_001 (event);

Ainda falta um índice, mas para isso me informa o que contém a variável $data na query abaixo:

$sql1="SELECT time,callid,arg2 FROM queue_log where $data and callid = '". $registro["callid"] . "' and event='ENTERQUEUE'";

[Eduardo Assis]

$data = "time between '2008-12-06 00:00:00' and '2008-12-06 23:59:59'";

[Denis Courcy]

$data = "time between '2008-12-06 00:00:00' and '2008-12-06 23:59:59'";

Então, juntando o que você passou acima com seu SQL abaixo :

$sql1="SELECT time,callid,arg2 FROM queue_log where $data and callid = '". $registro["callid"] . "' and event='ENTERQUEUE'";

Rode o script abaixo para a inclusão de mais um índice:

ALTER TABLE queue_log 
ADD INDEX queue_log_002 (time, callid, event)

Mais uma coisa, pois estou vendo que voê está usando código para resolver coisas que já poderiam ser retornadas em seu SQL.

Dê uma olhada no manual do MySQL versão 4.1 (tradução em português) seção 6.3.4. Funções de Data e Hora

DATEDIFF(expr,expr2), TIMEDIFF(expr,expr2)

DATEDIFF() retorna o número de dias entre a data inicial expr e a data final expr2. expr e

expr2 são expressões de datas ou data e hora. Apenas a parte da data dos valores sã usados no

cálculo.

TIMEDIFF() retorna o tempo entre a hora inicial expr e a hora final expr2. expr e expr2

são expressões de hora ou data e hora, mas ambas devem ser do mesmo tipo.

mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');

-> 1

mysql> SELECT DATEDIFF('1997-11-31 23:59:59','1997-12-31');

-> -30

mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');

-> '-00:00:00.000001'

mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001','1997-12-30 01:01:01.000002');

-> '46:58:57.999999'

[Eduardo Assis]

Agradeço sua ajuda Denis Courcy.

Irei fazer as alterações e ver se fica mais rapido e usar direto no mysql sem o between.

Abraços.