Selecting mutual rows between 20 mysql databases

Question

I have very large databases, 10 databases for users each one containing only one table called index.

SELECT * FROM db1, db2, db3, db4, db5, db6 WHERE db1.index.user_id, db2.index.user_id, db3.index.user_id = db4.index.user_id, db5.index.user_id, db6.index.user_id ORDER BY db1.index.name, db2.index.name, db3.index.name

is there is a way to make this code ???

Table index in user databases:

database1
user_id   | name | country
.....................................
198       |User1 | egypt

database2
user_id   | name | country
.....................................
236       |User2 | uk

database3
user_id   | name | country
.....................................
385       |User3 | usa

and another 3 databases for status updates.

Table index in status update databases:

database4
user_id | status_update | date
.....................................
198     |xxxxx          | 2011-08-24 13:00
198     |xxxxxxx        | 2011-08-24 10:33
236     |xxxxxxx        | 2011-08-24 06:33

database5
user_id | status_update | date
.....................................
198     |xxxxx          | 2011-08-24 15:01
385     |xxxxxxx        | 2011-08-24 10:33
305     |xxxxx          | 2011-08-24 12:11

database6
user_id | status_update | date
.....................................
400     |xxxxxxx        | 2011-08-24 10:39
236     |xxxxx          | 2011-08-24 09:00
981     |xxxxxxx        | 2011-08-23 22:54

I want to select from the both user databases and status update databases the mutual or common user_id (users) between the user databases and status update databases.

SELECT * FROM db1, db2, db3, db4, db5, db6 WHERE db1.index.user_id, db2.index.user_id, db3.index.user_id = db4.index.user_id, db5.index.user_id, db6.index.user_id ORDER BY db1.index.name, db2.index.name, db3.index.name

is there is a way to make this code ???

Answer 1

Here is something that might accomplish what you want for 3 databases. You can follow the query pattern for joining more than 3 databases.

SELECT * FROM database1.index LEFT JOIN database2.index ON database1.index.user_id=database2.index.user_id LEFT JOIN database3.index ON database1.index.user_id=database3.index.user_id;

Answer 2

<?php

$hostname = 'yourHostname';
$username = 'yourUsername';
$password = 'yourPassword';
$databases = array('database1', 'database2', 'database3', ...);

// connect to all databases and select db
$firstConnect = true;
$links = array();
foreach ($databases as $database) {
  $links[$database] = mysql_connect($hostname, $username, $password, !$firstConnect); 
  $firstConnect = false;
  mysql_select_db($database, $links[$database]);
}

// select from all databases
$users = array();
foreach ($databases as $database) {
  $result = mysql_query('select * from index', $links[$database]);
  while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    $users[] = $row;
  }
} 

$statusDatabases = array('status_update_db1', 'status_update_db2', 'status_update_db3', ...);
// same steps as above
// get status_update records in $statusUpdates
// do some mix and match betwen $users and $statusUpdates

There must be a better way

---

EDIT: If your databases are 9GB, this will take forever, it is not a viable solution