I'm trying to get a single MySQL query result from different queries working on different table and databases, this is my table structure with database names:
database ads1
-
tables: clicks_214
,requests
database ads2
-
tables: clicks_255
,requests
database ads3
- tables: clicks_256
,requests
and this is the query which I'm trying to execute
$query="";
for($i=1;$i<4;++$i)
{
$this->db_num=$i;
$this->selectDB($i);
$table=$this->getTableName();
$db=$this->getDatabaseName();
$query.="(SELECT r.kwd, count(c.rid) as cnum, count(r.kwd) as rnum
FROM $db.requests r LEFT JOIN $db.$table c ON r.id=c.rid
WHERE hid='$hid'
AND r.time BETWEEN '$date1 $hour1:00:00' AND '$date2 $hour2:59:59'
GROUP BY r.kwd
ORDER BY cnum DESC
LIMIT $limit,50)";
if($i<3)
{
$query.=" UNION ";
}
}
I'm certainly sure that this isn't the best way to do it, just because I have to wait about 5 minutes to get results. Is there any way to do this much faster? I've already set indexes on all of the 3 DBs
this is an EXPLAIN result: