I need this sql more fast!!

Thanks!!

Roger.


================================================================================
SQL statement (Executed with 4.25 Build 5)
================================================================================

SELECT ID_AREA,
      ID_OPERACAO,
      CAP.LIMITE,
      AVG(DURACAO) - AVG(TRANSPORTE) TRABALHO,
      CAP.TRANSPORTE,
      AVG(DURACAO) TOTAL,
      IF(COUNT(*) > 1 THEN COUNT(*) ELSE 0) TOTALREG,
      SUM(IF(EXCEDIDO > 0 THEN 1 ELSE 0)) QUANTIDADE,
      SUM(EXCEDIDO) MINUTOS, SUM(EXCEDIDO) / SUM(IF(EXCEDIDO > 0 THEN 1 ELSE 0)) MTTR,

      (SUM(DURACAO) / SUM(IF(EXCEDIDO > 0 THEN 1 ELSE 0))) / (3600 / AVG(G.JPH)) MCBF,

      C.DESCRICAO

FROM OPERACOES O

LEFT OUTER JOIN PROCESSADOS P ON (O.ID_OPERACAO = P.OPERACAO_ID AND (INICIO >=
'2007-10-10 06:00:00' AND TERMINO <= '2007-10-10 16:10:00') AND AREA_ID = 2 AND
P.TURNO IN(1,2,3))
LEFT OUTER JOIN CAPTURAR CAP ON (O.ID_AREA = CAP.ID_CELULA) AND (O.ID_OPERACAO
= CAP.ID_OPERACAO)
LEFT OUTER JOIN CELULAS C ON (O.ID_AREA = C.ID)
LEFT OUTER JOIN GRUPOS G ON (C.GRUPO = G.ID)

WHERE O.ID_AREA = 2

GROUP BY ID_OPERACAO
ORDER BY ID_OPERACAO

Tables Involved
---------------

OPERACOES (O) table opened shared, has 59 rows
PROCESSADOS (P) table opened shared, has 299338 rows
CAPTURAR (CAP) table opened shared, has 56 rows
CELULAS (C) table opened shared, has 12 rows
GRUPOS (G) table opened shared, has 24 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more grouped rows

Result set will be grouped by the following column(s) using a temporary index:

ID_OPERACAO

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

ID_OPERACAO ASC

WHERE Clause Execution
----------------------

The expression:

O.ID_AREA = 2

is OPTIMIZED, covers 6 rows or index keys, costs 84 bytes, and will be applied
to the OPERACOES table (O) before any joins

Join Ordering
-------------

The driver table is the OPERACOES table (O)

The OPERACOES table (O) is joined to the PROCESSADOS table (P) with the LEFT
OUTER JOIN expression:

O.ID_OPERACAO = P.OPERACAO_ID AND INICIO >= '2007-10-10 06:00:00' AND TERMINO
<= '2007-10-10 16:10:00' AND AREA_ID = 2 AND P.TURNO IN (1 , 2 , 3)

The OPERACOES table (O) is joined to the CAPTURAR table (CAP) with the LEFT
OUTER JOIN expression:

O.ID_AREA = CAP.ID_CELULA AND O.ID_OPERACAO = CAP.ID_OPERACAO

The OPERACOES table (O) is joined to the CELULAS table (C) with the LEFT OUTER
JOIN expression:

O.ID_AREA = C.ID

The CELULAS table (C) is joined to the GRUPOS table (G) with the LEFT OUTER
JOIN expression:

C.GRUPO = G.ID

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

Join Execution
--------------

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the
optimizer to consider costs when optimizing this join

The expression:

O.ID_OPERACAO = P.OPERACAO_ID AND INICIO >= '2007-10-10 06:00:00' AND TERMINO
<= '2007-10-10 16:10:00' AND AREA_ID = 2 AND P.TURNO IN (1 , 2 , 3)

has been rewritten and is OPTIMIZED

The expression:

O.ID_AREA = CAP.ID_CELULA AND O.ID_OPERACAO = CAP.ID_OPERACAO

is OPTIMIZED

The expression:

O.ID_AREA = C.ID

is OPTIMIZED

The expression:

C.GRUPO = G.ID

is OPTIMIZED

================================================================================
>>>>> 6 rows affected in 0,641 seconds
================================================================================

Roger,

<< I need this sql more fast!!  >>

How much faster do you need it to be ?  It already executes in
(approximately) half a second.

--
Tim Young
Elevate Software
www.elevatesoft.com