复制到临时表需要很长时间 [英] copying to temp table is taking long time

问题描述

实际上我使用这个临时表概念是因为我希望查询运行得很快.我的代码如下

actually m using this temp table concept becoz i want the query to run fast. my code is as follows

$maketemp1 = "
    CREATE TEMPORARY TABLE bin1 (
     `ackNo` varchar(55),
      `repairStatus` varchar(100),
      `productFamily` varchar(100),
      `location` varchar(100),

      `binTo` varchar(100),
      `binTime` datetime,
      `age` int(50)

       )
  "; 

 mysql_query($maketemp1) or die ("Sql error : ".mysql_error());

  $inserttemp1 = "
    INSERT INTO bin1
      (`ackNo`, `repairStatus`, `productFamily`, `location`,`binTo`,`binTime`,`age`)
    SELECT customerupdate.ackNo,tblRepairQueue.repairStatus, tblRepairQueue.productFamily,tblRepairQueue.location,customerupdate.binTo,customerupdate.binTime,
    TIMESTAMPDIFF(HOUR , customerupdate.binTime , '".$value."') FROM `tblRepairQueue` , `customerupdate` WHERE
tblRepairQueue.ackNo=customerupdate.ackNo  and tblRepairQueue.location='".$empLocationName."'

  ";

  mysql_query($inserttemp1) or die ("Sql error : ".mysql_error());

但是发生的事情是，当我运行上述查询时，复制时间太长了..与普通查询相比，该过程非常慢..而且我还有另一个疑问..使用临时表的概念会更快地给出结果吗?

But what is happening is when i run the above query it is taking too long time to copy ..the process is very slow when compared to normal query.. and also i have another doubt..will the concept of using temp table give results faster?

推荐答案

临时表可能不是最佳选择，具体取决于您要实现的目标...创建包含一些数据合并以缩小范围的表可能会很有用下几个请求的范围，但这样做效率很低.

Temporary table might not be the best option depending on what you are trying to achieve... It can become useful to create a table with some data consolidation to narrow the scope of the next couple requests but it is rarely efficient to do so.

另外，临时表不能被多次访问，所以需要一个真正的表.

Also, temporary tables cannot be accessed multiple times, so a real table would be needed.

表的创建需要磁盘写入，这比 RAM 中的纯选择效率低很多.使用缓存方法，您可以非常快速地检索先前选择的结果.您可以使用该选择作为查询的 FROM 部分，使其比在庞大的数据库中池化更快.但即便如此...

The creation of a table requires disk writes, which is a lot less efficient than a pure select in in the RAM. Using caching methods, you can retrieve the result of a previous select very fast. You could use that select as the FROM part of your queries to make it faster than pooling in a huge database. But even then...

我们正在开发一个大约 1To 的数据集，大查询是根据用户的权限(类别、商店、...)发现产品我们试图为每个用户创建一个表，指向他拥有的产品然后使用该表来检索数据，而无需大量 where 条件，但使用 SSD 仍然很慢.所以我们只是在服务器上转储了巨大的 RAM，我们将用来填充临时表的 SELECT 在登录时为每个用户运行，然后用作 FROM，一切都很完美.

We are evolving on a dataset of approximately 1To, big queries are findings products according to the users' rights (category,store,...) we have tried to create a table for each user pointing to the products he has the rights on and then use that table to retrieve the data without requiring huge where conditions but it was still slow using SSD. So we just dumped huge RAM on the server and the SELECT that we would have used to fill the temp tables is ran for each user at login, and then used as FROM and everything's perfect.

这篇关于复制到临时表需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！