Re: query taking time to execute...

From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Tue, 09 Dec 2014 23:09:40 -0500
Message-ID: <m68h1j$1e1$1_at_dont-email.me>

On 12/9/2014 10:34 PM, nag wrote:
>
> Hi
> I am new to mysql. I have a following code, which is taking bit lot time for execution.
>
> Is there any other way to write the same code in a simple way.
>
> Here we are creating a temporary table y from which we are extracting the desired result. (we have around 5 million recrods database)
>
>
> $query11=mysql_query("create temporary table y (select agnt,shnm,dtoc,dobt,count(*) as dup from master where agnt='$agnt' and dtoc between '19890401' and '19900331' and stat not in (11,12,84) group by agnt,dtoc,shnm,dobt having dup >1)");
>
> $query12=mysql_query("select a.agnt,a.shnm,a.dtoc,a.dobt from y, master as a where y.agnt = a.agnt and y.shnm = a.shnm and y.dtoc = a.dtoc and y.dobt = a.dobt and a.dtoc between '19890341' and '1990331' and stat not in (11,12,84)");
> while ($row=mysql_fetch_array($query12)){ if($row[0]>0){$dup++;}}
> ..
> ...
> ....
>
> Please help.
>

I'm not sure you need a temporary table here - and that's going to take time. You should be able to do it all in one query.

So first of all - what are your table definitions (including indexes)? What is a sample of the data? And what output do you want?

Also, you should learn to use JOIN syntax in your statements, instead of trying to do it in your WHERE clause. It will make things a lot easier to understand (as will properly formatting your SQL for reading).

Something like:

SELECT a.agnt,a.shnm,a.dtoc,a.dobt
FROM y
JOIN master a ON

  y.agnt = a.agnt AND
  y.shnm = a.shnm AND
  y.dtoc = a.dtoc AND
  y.dobt = a.dobt

WHERE a.dtoc between '19890341' and '1990331' AND

   stat not in (11,12,84)

Can you see where this is much easier to read and understand?

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================

Received on Wed Dec 10 2014 - 05:09:40 CET