mysql php : switching between mysql databases is slow

Question

In my php script which connects to mysql, I have to query 2 databases in the same script to get different information. More specifically Faxarchives in one database and Faxusers in the other.

In my code I query faxusers and then foreach user, I query Faxarchives to get the user history.

I might do something like:

function getUserarchive( $userid) { 
  $out= ""; 
  $dbname = 'Faxarchive';
  $db = mysql_select_db($dbname);
  $sql = "select sent, received from faxarchivetable where userid = '" . $userid . "'"; 

  if ( $rs = mysql_query($sql) { 
    while ($row = mysql_fetch_array($rs) ) { 
      $out = $row['sent'] . " " . $row['received']; 
    }//end while 
  }//end if query

  return ($out); 
}//end function 


$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'Faxusers';
$db = mysql_select_db($dbname);
$sql="select distinct userid from faxuserstable"; 
if ( $rs = mysql_query($sql) { 
  while ($row = mysql_fetch_array($rs) ) { 
    $out = $row['userid'] . ":" . getuserarchive($row['userid']); 
  }//end while 
}//end if query

I'm guessing the switching between databases for each user is causing the slowness. Anyways how i can improve the speed of the processing?

thanks in advance.

Answer 1

You have several problems. First, your function, getUserarchive, pulls back all rows all the time. Looks like you forgot to restrict your SQL to only a specific userid. That's a big cause of your speed issues.

Another option would be to pass in all userids you are interested in. Rewrite the SQL as SELECT sent, received FROM faxarchivetable WHERE userid IN (...), which means you'd have one select to pull back all info from your faxarchivetable, instead of one per each userid.

Finally, if both databases are on the same MySQL server, you could just do a single select to pull in all the information: SELECT Faxusers.faxuserstable.userid, sent, received FROM Faxusers.faxuserstable JOIN Faxarchive.faxarchivetable ON Faxusers.faxuserstable.userid = Faxarchive.faxarchivetable.userid

Answer 2

Are you aware that you can query tables in separate databases by qualifying the tablename?

For example, I think you can get all your information in a single SQL query like this:

SELECT sent, received 
FROM Faxarchive.faxarchivetable
WHERE userid IN ( SELECT DISTINCT userid FROM Faxusers.faxuserstable );

Oh, this is the same point ChrisInEdmonton makes. I didn't notice his answer.

Answer 3

'm looking at the query : select sent, received from faxarchivetable where userid in ( select distinct userid from faxuserstable).. each of the tables are in different databases. Wouldn't I need to do a mysql_select_db before running each query?

Answer 4

Is there a suggested reading to learn more about how to use joins?