Need to rewrite this into 1 faster query

[scarhand]

I build an array and then foreach array item I perform a large query. I need to write this into 1 query but don't know how. The problem is this page is taking a long time to load because it is performing 1 query, then its performing another query for each result of the first query via a foreach loop.

 

Heres the code:

$arr_websites = array();
$sql = mysql_query("select `website` from `leads` group by `website` asc");

while ($row = mysql_fetch_assoc($sql))
{
    if (!empty($row['website']))
        $arr_websites[] = $row['website'];
}

foreach ($arr_websites as $key => $ws)
{
    $sql = mysql_query("select 
                        l.website,
                        sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now(), '%Y-%m-%d') then 1 else 0 end) AS c_day,
                        sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 1 day, '%Y-%m-%d') then 1 else 0 end) AS c_yesterday,
                        sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 2 day, '%Y-%m-%d') then 1 else 0 end) AS c_2_days,
                        sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 3 day, '%Y-%m-%d') then 1 else 0 end) AS c_3_days,
                        sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 4 day, '%Y-%m-%d') then 1 else 0 end) AS c_4_days,
                        sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 5 day, '%Y-%m-%d') then 1 else 0 end) AS c_5_days,
                        sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 6 day, '%Y-%m-%d') then 1 else 0 end) AS c_6_days,
                        sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 7 day, '%Y-%m-%d') then 1 else 0 end) AS c_7_days,
                        sum(case when YEARWEEK(FROM_UNIXTIME(COALESCE(a.date_assigned, l.date_added))) = YEARWEEK(CURDATE()) then 1 else 0 end) AS c_week, 
                        sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now(), '%Y-%m') then 1 else 0 end) AS c_month,
                        sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now() - interval 1 month, '%Y-%m') then 1 else 0 end) AS c_last_month,
                        sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now() - interval 2 month, '%Y-%m') then 1 else 0 end) AS c_2_months,
                        sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now() - interval 3 month, '%Y-%m') then 1 else 0 end) AS c_3_months,
                        sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y')= date_format(now(), '%Y') then 1 else 0 end) AS c_year  
                        from `leads` as l
                        left join `assignments` as a on (a.id_lead = l.id)
                        left join `dealerships` as d on (d.id = a.id_dealership)
                        where a.is_reassign='no' and a.id_dealership!='65' and a.id_dealership!='77' and a.id_dealership!='89' and a.id_dealership!='138' and (a.website='$ws' or l.website='$ws')
                        ") or die(mysql_error());
}

I don't know how to get this to have the correct sums. The problem is that the "leads" table and then "assignments" table both have a "website" field, sometimes the website field may be empty in one or the other which is why you see the code and (a.website='$ws' or l.website='$ws'), where $ws is the result of the first query that is being looped in the foreach for the second query. The assignments have an id_lead field which links the assignments to a lead.

 

Heres some things I've tried which makes the page faster, but does not produce the correct sum's:

 

- i've tried grouping by l.website

- i've tried a left join with the code left join assignments as a on (a.id_lead = l.id AND a.website=l.website) 

 

I hope this makes sense, any help would be greatly appreciated.

[Psycho]

Um, you should probably also look at the SELECT part of the query - I'm sure all that nested functionality has an impact. Anyway, you can prevent running this in loops by using the IN() clause. Build up a list of the $ws values and run the query one time looking for any matches of that list.

 

 

//Create string of comma separated website values enclosed in quotes
$websitesList = "'" . implode("', '", $arr_websites) . "'";

$sql = "select l.website,
               sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now(), '%Y-%m-%d') then 1 else 0 end) AS c_day,
               sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 1 day, '%Y-%m-%d') then 1 else 0 end) AS c_yesterday,
               sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 2 day, '%Y-%m-%d') then 1 else 0 end) AS c_2_days,
               sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 3 day, '%Y-%m-%d') then 1 else 0 end) AS c_3_days,
               sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 4 day, '%Y-%m-%d') then 1 else 0 end) AS c_4_days,
               sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 5 day, '%Y-%m-%d') then 1 else 0 end) AS c_5_days,
               sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 6 day, '%Y-%m-%d') then 1 else 0 end) AS c_6_days,
               sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m-%d') = date_format(now() - interval 7 day, '%Y-%m-%d') then 1 else 0 end) AS c_7_days,
               sum(case when YEARWEEK(FROM_UNIXTIME(COALESCE(a.date_assigned, l.date_added))) = YEARWEEK(CURDATE()) then 1 else 0 end) AS c_week,
               sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now(), '%Y-%m') then 1 else 0 end) AS c_month,
               sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now() - interval 1 month, '%Y-%m') then 1 else 0 end) AS c_last_month,
               sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now() - interval 2 month, '%Y-%m') then 1 else 0 end) AS c_2_months,
               sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y-%m')= date_format(now() - interval 3 month, '%Y-%m') then 1 else 0 end) AS c_3_months,
               sum(case when date_format(from_unixtime(COALESCE(a.date_assigned, l.date_added)), '%Y')= date_format(now(), '%Y') then 1 else 0 end) AS c_year  
            FROM `leads` AS l
            LEFT JOIN `assignments` as a on (a.id_lead = l.id)
            LEFT JOIN `dealerships` as d on (d.id = a.id_dealership)
            WHERE a.is_reassign='no'
              AND a.id_dealership!='65'
              AND a.id_dealership!='77'
              AND a.id_dealership!='89'
              AND a.id_dealership!='138'
              AND (a.website IN ({$websitesList}) OR l.website IN ({$websitesList})";
$result = mysql_query($sql) or die(mysql_error());