MySQL Union查询需要花费太多时间来执行

问题描述：

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

我正在尝试从不同的表和数据库上运行的不同查询获得单个MySQL查询结果，这是我的具有数据库名称的表结构： p>

数据库ads1 code> - tables： clicks_214 code>， requests code> p >

数据库 ads2 code> - tables： clicks_255 code>， requests code> p>

数据库 ads3 code> - tables： clicks_256 code>， requests code> p>

这是我正在尝试的查询执行 p>


  $ query =“”; 
for（$ i = 1; $ i＆lt; 4; ++ $ i）
 {
 $ this-＆gt; db_num  = $ i; 
 $ this-＆gt; selectDB（$ i）; 
 $ table = $ this-＆gt; getTableName（）; 
 $ db = $ this-＆gt; 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 \  n WHERE hid ='$ hid'
和r.time BETWEEN'$ date1 $ hour1：00：00''' $ date2 $ hour2：59：59' 
  GROUP BY r.kwd 
 ORDER BY cnum DESC 
 LIMIT $ limit，50）“; 
 if（$ i＆lt; 3）
 {
 $ query。=”UNION“; 
} 
} \  n  code>  pre> 
 
 我当然确定这不是最好的方法，因为我必须等待大约5分钟才能得到结果。 有没有办法更快地做到这一点？ 我已经在所有3个DB上设置了索引 p> 
 
 
这是一个EXPLAIN结果：
  p> 
  div>

答

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());

MySQL Union查询需要花费太多时间来执行

相关推荐