Execute multiple parallel queries??

KendersPlace

Hello all. The application I am currently working on executes a query on the local box that takes about 15 to 20 seconds to complete. (Query a table with several million records). The returned data is valuable, so the slight delay on this page for the query to run is acceptable.

... but it gets complicated...

Now I need to run the same query on several different databases with the same type of data, but different record sets.

I would like to issue the query to multiple servers all at once, then each server can run it's own process, and all servers should return the result at about the same time. I currently need to run this query on 5 separate database on 5 separate boxes at once.

PHP4, MySql, Linux boxes. Right now, if I run the query, wait for results, then run the next, and so on.... this page will take 60 to 90 seconds to display, but that is too long.

I'd like to run them all in parallel. Tell PHP to issue all 5 queries, then drop the results into record sets as they are returned from their perspective servers, then continue on with the page execution.

I have looked at options that do not wait for a returned result, but this won't work, as I do actually need the returned result.

Any help?

Thanks.

Sxooter

If you were using postgresql you could use:

pg_send_query

I don't know how to do it in MySQL, as it doesn't support async queries.

tha_mink

just make 5 conncections and query them all at once. Like so..(using pear)


$db1 = DB::connect( "mysql://$dbuser1:$dbpass1@$dbhost1/$dbname1" );

$db2 = DB::connect( "mysql://$dbuser2:$dbpass2@$dbhost2/$dbname2" );
$db3 = DB::connect( "mysql://$dbuser3:$dbpass3@$dbhost3/$dbname3" );
$db4 = DB::connect( "mysql://$dbuser4:$dbpass4@$dbhost4/$dbname4" );
$db5 = DB::connect( "mysql://$dbuser5:$dbpass5@$dbhost5/$dbname5" );

$q1 = "SELECT something FROM sometable";
$q2 = "SELECT something FROM sometable";
$q3= "SELECT something FROM sometable";
$q4= "SELECT something FROM sometable";
$q5= "SELECT something FROM sometable";

$res1  =  $db1->query($q1);
$res2  =  $db2->query($q2);
$res3  =  $db3->query($q3);
$res4  =  $db4->query($q4);
$res5  =  $db5->query($q5);

Or if you don't want to have 5 connections (plus the memory to hold the result sets) Just loop the connections and queries.

KendersPlace

Outstanding. Thanks. I haven't tried this, but it makes sense, and I assume it will work just fine.

Thank you.