Query on large mysql database
===

i've got a script which is supposed to run through a mysql database and preform a certain 'test'on the cases. Simplified the database contains records which represent trips that have been made by persons. Each record is a singel trip. But I want to use only roundway trips. So I need to search the database and match two trips to each other; the trip to and the trip from a certain location.

The script is working fine. The problem is that the database contains more then 600.000 cases. I know this should be avoided if possible. But for the purpose of this script and the use of the database records later on, everything has to stick together.

Executing the script takes hours right now, when executing on my iMac using MAMP. Off course I made sure that it can use a lot of memory etcetare.

My question is how could I speed things up, what's the best approach to do this?

Here's the script I have right now:

    $table          = $_GET['table'];                  
    $output = '';                  
    //Select all cases that has not been marked as invalid in previous test         
    $query = "SELECT persid, ritid, vertpc, aankpc, jaar, maand, dag FROM MON.$table WHERE reasonInvalid != '1' OR reasonInvalid IS NULL";         
    $result = mysql_query($query)or die($output .= mysql_error());                    
    $totalCountValid = '';         
    $totalCountInvalid = '';         
    $totalCount = '';                  
    //For each record:         
    while($row = mysql_fetch_array($result)){                 
        $totalCount += 1;                 
        //Do another query, get all the rows for this persons ID and that share postal codes. Postal codes revert between the two trips                 
       $persid                 = $row['persid'];                 
       $ritid                  = $row['ritid'];                 
       $pcD                    = $row['vertpc'];                 
       $pcA                    = $row['aankpc'];                 
       $jaar                   = $row['jaar'];                 
       $maand                  = $row['maand'];                 
       $dag                    = $row['dag'];         
       $thecountquery  = "SELECT * FROM MON.$table WHERE persid=$persid AND vertpc=$pcA AND aankpc=$pcD AND jaar = $jaar AND maand = $maand AND dag = $dag";                  
       $thecount               = mysql_num_rows(mysql_query($thecountquery));                 
       if($thecount >= 1){                         
          //No worries, this person ID has multiple trips attached                            
          $totalCountValid += 1;                 
       }else{                         
          //Ow my, the case is invalid!                         
         $totalCountInvalid += 1;                         
         //Call the markInvalid from functions.php                          
         $totalCountValid += 1;                          
         markInvalid($table, '2', 'ritid', $ritid);                 
       }         
    }                  
    //Echo the result         
    $output .= 'Total cases: '.$totalCount.'<br>Valid: '.$totalCountValid.'<br>Invalid: '.$totalCountInvalid;                  echo $output; 