making this query run faster?

Hello,
The following brief script takes 35 seconds to run.
If there something I might be able to do to make it run faster?
Thanks for any suggestions!
-Liam

$sql = "SELECT affiliates.accountid AS theaccount, count(ordernum) AS
thecount
FROM orders
JOIN affiliates ON (affiliates.accountid = orders.accountid)
WHERE complete = '1' AND date > '2007-07-01' AND
affiliates.accountid <> 'NULL'
GROUP BY theaccount
ORDER BY thecount desc
LIMIT 10";

$result = @mysql_query($sql);

while ($row1 = mysql_fetch_array($result)) {
$theaccount = $row1['theaccount'];
$thecount = $row1['thecount'];

$arr_msg1[] = "$theaccount : $thecount
";
$arr_msg2[] = "$theaccount : $thecount
";
}

foreach ($arr_msg1 as $arr_msg1) {
echo "$arr_msg1";
}

echo "

";

shuffle($arr_msg2);

foreach ($arr_msg2 as $arr_msg2) {
echo "$arr_msg2";
}

Re: making this query run faster?

"news@celticbear.com" wrote in
news:1187990370.234283.90500@q4g2000prc.googlegroups.com:

> Hello,
> The following brief script takes 35 seconds to run.
> If there something I might be able to do to make it run faster?
> Thanks for any suggestions!

Try asking a SQL/MySQL group, its not PHP related. You can start by making
sure your tables are correctly indexed; "EXPLAIN " is your friend.

Re: making this query run faster?

news@celticbear.com wrote:
> Hello,
> The following brief script takes 35 seconds to run.
> If there something I might be able to do to make it run faster?
> Thanks for any suggestions!
> -Liam
>
> $sql = "SELECT affiliates.accountid AS theaccount, count(ordernum) AS
> thecount
> FROM orders
> JOIN affiliates ON (affiliates.accountid = orders.accountid)
> WHERE complete = '1' AND date > '2007-07-01' AND
> affiliates.accountid <> 'NULL'
> GROUP BY theaccount
> ORDER BY thecount desc
> LIMIT 10";
>
> $result = @mysql_query($sql);
>
> while ($row1 = mysql_fetch_array($result)) {
> $theaccount = $row1['theaccount'];
> $thecount = $row1['thecount'];
>
> $arr_msg1[] = "$theaccount : $thecount
";
> $arr_msg2[] = "$theaccount : $thecount
";
> }
>
> foreach ($arr_msg1 as $arr_msg1) {
> echo "$arr_msg1";
> }
>
> echo "

";
>
> shuffle($arr_msg2);
>
> foreach ($arr_msg2 as $arr_msg2) {
> echo "$arr_msg2";
> }
>

Just a few random notes.

From your query, it's not clear why you need to join 'affiliates', all
info you need is already in 'orders' table.

accountid <> 'NULL' is probably not what you meant.

All fields you use in JOIN or WHERE clauses should be indexed.

Use EXPLAIN to find out why the query is slow.

Ask sql questions in sql group. ;)


--
gosha bine

extended php parser ~ http://code.google.com/p/pihipi
blok ~ http://www.tagarga.com/blok