optimizing the query into one

Question

can anybody help me to reduce this code.

$dat=  mysql_query
("SELECT wid,count(*) as count 
  FROM uptime 
  WHERE time_stamp>=DATE_SUB(NOW(),INTERVAL 10 MINUTE) 
  AND  status<>200 GROUP BY wid ORDER BY  time_stamp ASC"
);

while($ans= mysql_fetch_assoc($dat))
{
   $count_array[]=$ans;
}

foreach ($count_array as $value) 
{
   if($value['count']==2)
   {
      $id=$value['wid'];
      $add=  mysql_query("SELECT email FROM website WHERE webid='".$id."'");
      $result=  mysql_fetch_assoc($add);
      <--etc etc to send email-->
    }
}

I need to reduce the code please help me.if the two queries can be combined without losing the meaning will also be helpful for me.

Start with removing empty lines. It will almost reduce this code twice. — zerkms, Sep 12 '13 at 11:12
"I want" --- is there some technical requirement or you just "want" it without any particular reason? — zerkms, Sep 12 '13 at 11:13
PS: there are not just 2 queries in total. The second one will be executed N times. — zerkms, Sep 12 '13 at 11:15

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

That's a simple join between the two tables:

select
    upity.wid,
    webit.email,
    count(*) as count
from
    uptime upity
        join website webby
            on upity.wid=webby.webid
where
    upity.time_stamp>=DATE_SUB(NOW(),INTERVAL 10 MINUTE) 
    and upity.status<>200
group by
    upity.wid,
    webit.email
having count=2

I wrote a really in-depth Q&A on joining tables in SQL which will explain in a lot more detail how this all works. In fact, I wrote it for exactly this reason - be able to help out with a quick query, and then be able to link to a detailed explanation of what is going on.

Edit: As per the comments below between Zerkms and myself, if you take this code, do have a look at the execution times. Large tables can lead to very large datasets when joined and it may indeed be possible that running multiple queries could be more efficient.

edited May 23 '17 at 12:05

Community

1
1

answered Sep 12 '13 at 11:16

Fluffeh

33,228
16
67
80

So it's a `JOIN` with `GROUP BY`. Why would it be better than 2 trivial queries? – zerkms Sep 12 '13 at 11:18
@zerkms Because it's not just two queries, but a second query that could potentially be run hundreds/thousands of times inside the loop? – Fluffeh Sep 12 '13 at 11:20
you can use `webid IN ()` and that will be almost instant. Whereas `JOIN` with `GROUP BY` will lead to a terrible execution plan. – zerkms Sep 12 '13 at 11:20
If there are loads of rows matched, how would running an almost instant query be better than joining the tables on the same primary keys and running it as a single query? – Fluffeh Sep 12 '13 at 11:23
do you realize how `GROUP BY` by 2 keys that are in the different tables will work? – zerkms Sep 12 '13 at 11:23
If you mean the technical data, no. I would assume it writes the collected rows to a temp table and then sorts through them to aggregate? – Fluffeh Sep 12 '13 at 11:25
yep, something like that. And the `JOIN`ed result set (the one after `JOIN` + `WHERE` but before `GROUP BY` and `HAVING`) could be **MUCH BIGGER** than the result set from the first query. – zerkms Sep 12 '13 at 11:25
1

Not saying it isn't possible, but I would think it was a rare instance where it would be more efficient to run many quieries. If it was mine and this came up, I would certainly run both to see how it functions and go with the better option. Surely if his code takes 2 seconds to execute now, and a change makes it run for 20, it's obvious that it really isn't more efficient :) – Fluffeh Sep 12 '13 at 11:28
1

"I would certainly run both to see" --- indeed. That's why it worth telling to OP that 2 queries don't generally worse than one. The opposite is true as well. I started discussing it here because for me it's obvious OP tries to perform some "optimization" without understanding if it's required or even worth trying. – zerkms Sep 12 '13 at 11:29
now I can go to sleep - another potential OP misleading is avoided :-) +1 – zerkms Sep 12 '13 at 11:35
@zerkms Have a good one, and nice to see you keeping me honest. I answer to learn and it's always nice to be reminded that things aren't black and white all the time. – Fluffeh Sep 12 '13 at 11:37

score 0 · Answer 2 · answered Sep 12 '13 at 11:21

$dat=  mysql_query("SELECT wid,count(*) as count 
FROM uptime WHERE time_stamp>=DATE_SUB(NOW(),INTERVAL 10 MINUTE) AND  status<>200 GROUP BY wid ORDER BY  time_stamp ASC");
while($ans= mysql_fetch_assoc($dat)){
if($ans['count']==2){
  $id=$value['wid'];
 $add=  mysql_query("SELECT email FROM website WHERE webid='".$id."'");
$result=  mysql_fetch_assoc($add);
<--etc etc to send email-->
}
}

score 0 · Answer 3 · answered Sep 12 '13 at 11:27

Try this one:

$dat = mysql_query("
SELECT 
    ut.wid,ut.count(*) as count,ws.email 
FROM 
     uptime as ut 
INNER JOIN
     website as ws 
ON  
     ut.id=ws.webid  
WHERE 
     ut.time_stamp>=DATE_SUB(NOW(),INTERVAL 10 MINUTE) AND  ut.status<>200 AND ut.count=2 
GROUP BY  
      ut.wid  
ORDER BY  
       ut.time_stamp ASC");

optimizing the query into one

3 Answers3