Mensagem sobre o tópico
[OFF] Mysql melhoria de performance
Received: by 10.35.22.9 with SMTP id z9mr2241216pyi.1.1210877751496;
Thu, 15 May 2008 11:55:51 -0700 (PDT)
Return-Path: <fre...@gmail.com>
Received: from el-out-1112.google.com (el-out-1112.google.com [209.85.162.183])
by mx.google.com with ESMTP id 7si23177745yxg.1.2008.05.15.11.55.50;
Thu, 15 May 2008 11:55:51 -0700 (PDT)
Received-SPF: pass (google.com: domain of fre...@gmail.com designates 209.85.162.183 as permitted sender) client-ip=209.85.162.183;
Authentication-Results: mx.google.com; spf=pass (google.com: domain of fre...@gmail.com designates 209.85.162.183 as permitted sender) smtp.mail=fre...@gmail.com; dkim=pass (test mode) header...@gmail.com
Received: by el-out-1112.google.com with SMTP id m34so201496ele.8
for <listaphp@googlegroups.com>; Thu, 15 May 2008 11:55:50 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=gmail.com; s=gamma;
h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:subject:references:in-reply-to:content-type:content-transfer-encoding;
bh=2qmclXfAXYgRIl4Dq7VnQCHCM9JH0zB1NIoU+Y6FOUE=;
b=GITVr+195ztMZbLsLYFpStP9NZQ2MiQf37HP4wJEjvQ8EO8Jskn5K5s/ewL8wPE6Pe6JLTlAV7E0kuZOiPMPPEpMvXkZX1ajle5s2jDhhG+blpf3e7HlfANrOr+SjLqzJLx8K1zbDTYqIG6wbdx+/KbCsREt3DgqAvY5bg2ZMAY=
DomainKey-Signature: a=rsa-sha1; c=nofws;
d=gmail.com; s=gamma;
h=message-id:date:from:user-agent:mime-version:to:subject:references:in-reply-to:content-type:content-transfer-encoding;
b=EoghG6aaGa4vuq+qAwe2NFvI9G5VK5WMXqs6Si08/j6e0fNe+kp8V8jgHG7mU2QGDvWOQi5GvMI1eVJlwq+g1y3hqtsFLcUresNo7Ew/Zd2WnCWpzPUTLJo7vb52k11JijLvyYvXiCGRfzUTr/uq8fMTYWnCpDDCHqA5jmZ/Ukw=
Received: by 10.115.60.1 with SMTP id n1mr2842125wak.181.1210877750091;
Thu, 15 May 2008 11:55:50 -0700 (PDT)
Return-Path: <fre...@gmail.com>
Received: from ?192.168.1.102? ( [201.29.248.107])
by mx.google.com with ESMTPS id 9sm4689728yxs.5.2008.05.15.11.55.48
(version=TLSv1/SSLv3 cipher=RC4-MD5);
Thu, 15 May 2008 11:55:49 -0700 (PDT)
Message-ID: <482C874A.8010...@gmail.com>
Date: Thu, 15 May 2008 15:56:10 -0300
From: "Frederico R. Martins" <fre...@gmail.com>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
To: listaphp@googlegroups.com
Subject: Re: [ PHP - Google ] [OFF] Mysql melhoria de performance
References: <ecb807fb-0832-4cf0-b7a8-b9cd243b0...@t54g2000hsg.googlegroups.com>
In-Reply-To: <ecb807fb-0832-4cf0-b7a8-b9cd243b0...@t54g2000hsg.googlegroups.com>
Bom voc=EA deve rever os seus indicies, ver se os campos mais pesquisados=20
est=E3o devidamente indexados...
E tamb=E9m dependendo da vers=E3o do mysql passe isso para stored... melhora=
=20
bastante a performance.
Vitor de Oliveira escreveu:
> Boa tarde pessoal estou com o seguinte problema na geracao de um
> relatorio,
> o Mysql esta demorando muito tempo para retornar resposta.
>
> Alguem sabe como eu poderia melhorar a performance??
>
> Segue abaixo a sql:
>
> SELECT
> d.oidTipo, t.nome as tipo, d.oidSubtipo, s.nome as subtipo,
>
> (select count(*)
> from denuncia de, encaminhamento en
> where en.oidDenuncia =3D de.oidDenuncia
>
> and date(de.dataSistema)
> BETWEEN date('2006-01-01') AND date('2006-01-12')
>
> and de.oidTipo =3D d.oidTipo
> and de.oidSubtipo =3D d.oidSubtipo
> and en.oidStatusRetorno =3D 3) as Improcedente,
> (select count(*)
> from denuncia de, encaminhamento en
> where en.oidDenuncia =3D de.oidDenuncia
>
> and date(de.dataSistema)
> BETWEEN date('2006-01-01') AND date('2006-01-12')
>
> and de.oidTipo =3D d.oidTipo
> and de.oidSubtipo =3D d.oidSubtipo
> and en.oidStatusRetorno =3D 4) as 'N=E3o Informado',
> (select count(*)
> from denuncia de, encaminhamento en
> where en.oidDenuncia =3D de.oidDenuncia
>
> and date(de.dataSistema)
> BETWEEN date('2006-01-01') AND date('2006-01-12')
>
> and de.oidTipo =3D d.oidTipo
> and de.oidSubtipo =3D d.oidSubtipo
> and en.oidStatusRetorno =3D 5) as 'Parcialmente Procedente',
> (select count(*)
> from denuncia de, encaminhamento en
> where en.oidDenuncia =3D de.oidDenuncia
>
> and date(de.dataSistema)
> BETWEEN date('2006-01-01') AND date('2006-01-12')
>
> and de.oidTipo =3D d.oidTipo
> and de.oidSubtipo =3D d.oidSubtipo
> and en.oidStatusRetorno =3D 6) as Procedente
>
> from denuncia d
> inner join tipo t on (d.oidTipo =3D t.oidtipo)
> inner join tiposubtipo ts on (ts.oidtipo =3D t.oidtipo)
> inner join subtipo s on ((s.oidSubtipo =3D ts.oidSubtipo) and
> (s.oidSubtipo =3D d.oidSubTipo))
> inner join encaminhamento e on (d.oidDenuncia =3D e.oidDenuncia)
> WHERE date(d.dataSistema)
> BETWEEN date('2006-01-01') AND date('2006-01-12')
> GROUP BY d.oidSubtipo
> ORDER BY t.nome,s.nome;
>
> >
> =20
|
