Posted by gosha bine on 08/25/07 08:23

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<br/>";
> $arr_msg2[] = "$theaccount : $thecount<br/>";
> }
>
> foreach ($arr_msg1 as $arr_msg1) {
> echo "$arr_msg1";
> }
>
> echo "<br/><br/>";
>
> 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

[Back to original message]