Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Posts
    4

    Angry Unanswered: Query optimization

    I need help optimizing some sql queries.
    I have a site that is query extensive and I need to quicken it up.
    The problem lies in the fact that Im querying a database with about 20,000 rows,
    Then I have to check each entry against another table.

    Lets say this is the main query, Its going through the table 'leads'
    $x = mysql_query("select `ID` FROM `leads` ");

    Im using a 'while' statement to fetch_arrays from that table.
    While ($z = $mysql_fetch_array($x)){
    Echo $z['ID'];
    }

    pretty simple right.
    Well now I have to check if '$x['ID']' is in another database That has also 20,000+ entries.

    This is an idea of what it looks like.

    $x = mysql_query("select `ID` FROM `leads` ");
    While ($z = $mysql_fetch_array($x)){

    ---> $zz = mysql_query("SELECT count(*) FROM `table_2' Where (loanid = '".$z['ID']."' )");
    ---> $count = mysql_result($cart,0);
    echo $count;
    }

    this is just a sample and not the real code.

    basiclly its querying over 20,000 times while looping through.
    I need to combine the tables or something.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a left outer join so that it will return a 0 when there are no matching loans for a given ID
    Code:
    select ID 
         , count(table_2.loanid) as loans
      from leads
    left outer
      join table_2
        on leads.ID 
         = table_2.loanid 
    group
        by ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •