CODEWITHSUNDEEP
phpmysqldatabase
CapitanFindus
CapitanFindus

Reputation: 1526

MySQL Union query taking too much time to execute

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: enter image description here

Upvotes: 0

Views: 729

Answers (1)

Artjoman
Artjoman

Reputation: 286

a) You SHOULD NOT make such crossdatabase queries. With your DB structure it would be more appropriate to run 3 independent queries and combine the result using some simple script Or to make a temporary table in one of the databases where you can combine data from every one of those

b) On large databases put some indexes to improve the speed. It is not the solution though...

You can try to play with Temp tables:

$this->selectDB('temptableDB');
$maketemp = "
CREATE TEMPORARY TABLE temp_table_1 (
  `kwd` int ,
  `rid` int,
)
"; 

mysql_query($maketemp, $connection) or die ("Sql error : ".mysql_error());

for($i=1;$i<4;++$i)
{
   $this->db_num=$i;
   $this->selectDB($i);
   $table=$this->getTableName();
   $db=$this->getDatabaseName();

   $inserttemp = "
    INSERT INTO temptableDB.temp_table_1
     (`kwd`, `rid`)
        SELECT r.kwd, c.rid
        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'
  ";

  mysql_query($inserttemp, $connection) or die ("Sql error : ".mysql_error());

 }

  $select = "
    SELECT kwd, count(rid) as cnum, count(kwd) as rnum
    FROM temp_table_1
    GROUP BY r.kwd
    ORDER BY cnum DESC
  ";
  $export = mysql_query($select, $connection) or die ("Sql error : ".mysql_error());

Upvotes: 1

Related Questions