making this query run faster?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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!

$sql = "SELECT affiliates.accountid AS theaccount, count(ordernum) AS
     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/>";


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

Re: making this query run faster?

Quoted text here. Click to load it

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

Re: making this query run faster? wrote:
Quoted text here. Click to load it

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 ~
blok ~

Site Timeline