文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >在一整天运行相同的MYSQL查询时，有更好的方法吗？

问在一整天运行相同的MYSQL查询时，有更好的方法吗？
EN

Stack Overflow用户
提问于 2019-04-04 12:34:08
回答 1查看 51关注 0票数 0

我正在为我的导演写一份报告。这是一个每小时细分的呼叫和代理，登录到我们的拨号系统，以便我们可以看到整个一天的表现。随着时间的推移，有越来越多的查询和越来越多的结果，使它非常缓慢。

有比用不同参数调用同一个查询更好的方法吗？

其中一个列是索引的，我只选择它。

COUNT()比使用num_rows计数行更快吗？

我只是不知道什么是最好的做法，以阻止我锤打这个数据库，并提高性能。

这是它的一个片段，但我还运行了另外30个类似的查询，从不同的表中提取不同的结果。只是需要一段时间才能装上。选择当天的所有内容，然后用PHP格式化数据，会更好吗？我不知道做这件事的最佳方法是什么。在下面的查询中，我选择了*，但是从那时起，我已经将其更改为只提取索引列，但这似乎没有多大区别。

代码语言：javascript
复制
$Callssql9to11 = "SELECT * FROM `outbound_log_today` WHERE `campaign_id`='003'
                        AND `call_date` BETWEEN (TIME '09:00:00') AND TIME ('11:00:00')";
    $Callssql9to11result = $conn->query($Callssql9to11);
    /////////////////11-12///////////////////////////
    $Callssql11to12 = "SELECT * FROM `outbound_log_today` WHERE `campaign_id`='003'
                        AND `call_date` BETWEEN (TIME '11:00:00') AND TIME ('12:00:00')";
    $Callssql11to12result = $conn->query($Callssql11to12);
    /////////////////12-13///////////////////////////
    $Callssql12to13 = "SELECT * FROM `outbound_log_today` WHERE `campaign_id`='003'
                        AND `call_date` BETWEEN (TIME '12:00:00') AND TIME ('13:00:00')";
    $Callssql12to13result = $conn->query($Callssql12to13);
    /////////////////13-14///////////////////////////
    $Callssql13to14 = "SELECT * FROM `outbound_log_today` WHERE `campaign_id`='003'
                        AND `call_date` BETWEEN (TIME '13:00:00') AND TIME ('14:00:00')";
    $Callssql13to14result = $conn->query($Callssql13to14);
    /////////////////14-15///////////////////////////
    $Callssql14to15 = "SELECT * FROM `outbound_log_today` WHERE `campaign_id`='003'
                        AND `call_date` BETWEEN (TIME '14:00:00') AND TIME ('15:00:00')";
    $Callssql14to15result = $conn->query($Callssql14to15);
    /////////////////15-16///////////////////////////
    $Callssql15to16 = "SELECT * FROM `outbound_log_today` WHERE `campaign_id`='003'
                        AND `call_date` BETWEEN (TIME '15:00:00') AND TIME ('16:00:00')";
    $Callssql15to16result = $conn->query($Callssql15to16);
    /////////////////16-17///////////////////////////
    $Callssql16to17 = "SELECT * FROM `outbound_log_today` WHERE `campaign_id`='003'
                        AND `call_date` BETWEEN (TIME '16:00:00') AND TIME ('17:00:00')";
    $Callssql16to17result = $conn->query($Callssql16to17);
    /////////////////17-18///////////////////////////
    $Callssql17to18 = "SELECT * FROM `outbound_log_today` WHERE `campaign_id`='003'
                        AND `call_date` BETWEEN (TIME '17:00:00') AND TIME ('18:00:00')";
    $Callssql17to18result = $conn->query($Callssql17to18);
    /////////////////18-20///////////////////////////
    $Callssql18to20 = "SELECT * FROM `outbound_log_today` WHERE `campaign_id`='003'
                        AND `call_date` BETWEEN (TIME '19:00:00') AND TIME ('20:00:00')";
    $Callssql18to20result = $conn->query($Callssql18to20);
    /////////////////ALL///////////////////////////
    $CallssqlALL = "SELECT * FROM `outbound_log_today` WHERE `campaign_id`='003'
                        AND `call_date` BETWEEN (TIME '09:00:00') AND TIME ('20:00:00')";
    $CallssqlALLresult = $conn->query($CallssqlALL);


///////////////////9-11///////////////////////////
    $Connectssql9to11 = "SELECT * FROM `outbound_log_today`WHERE `campaign_id`='003'
                        AND `call_date`BETWEEN (TIME '09:00:00') AND TIME ('11:00:00') AND `status` IN (SELECT (STATUS) FROM `status_combined`WHERE `human_answered`='Y')";
    $Connectssql9to11result = $conn->query($Connectssql9to11);


$Leadssql9to11 = "SELECT * FROM `outbound_log_today`WHERE `campaign_id`='003'
                        AND `call_date`BETWEEN (TIME '09:00:00') AND TIME ('11:00:00')
                        AND `status`='passed'";
    $Leadssql9to11result = $conn->query($Leadssql9to11);



$Agentssql9to11 = "SELECT DISTINCT USER FROM `outbound_log_today`WHERE `campaign_id`='003'
                        AND `call_date`BETWEEN (TIME '09:00:00') AND TIME ('11:00:00') AND `user` NOT IN ('VDAD')";
    $Agentssql9to11result = $conn->query($Agentssql9to11);

/////////////////// html //////////////////////////
    <tr>
        <td></td>
        <th scope="col">9 - 11</th>
        <th scope="col">11 - 12</th>
        <th scope="col">12 - 13</th>
        <th scope="col">13 - 14</th>
        <th scope="col">14 - 15</th>
        <th scope="col">15 - 16</th>
        <th scope="col">16 - 17</th>
        <th scope="col">17 - 18</th>
        <th scope="col">18 - 19</th>
        <th scope="col">Total</th>
    </tr>
    <tr>
        <th scope="row">Calls</th>
        <td><?php echo $Callssql9to11result->num_rows; ?></td>
        <td><?php echo $Callssql11to12result->num_rows; ?></td>
        <td><?php echo $Callssql12to13result->num_rows; ?></td>
        <td><?php echo $Callssql13to14result->num_rows; ?></td>
        <td><?php echo $Callssql14to15result->num_rows; ?></td>
        <td><?php echo $Callssql15to16result->num_rows; ?></td>
        <td><?php echo $Callssql16to17result->num_rows; ?></td>
        <td><?php echo $Callssql17to18result->num_rows; ?></td>
        <td><?php echo $Callssql18to20result->num_rows; ?></td>
        <td><?php echo $CallssqlALLresult->num_rows; ?></td>
    </tr>
    <tr>
        <th scope="row">Connect%</th>
        <td><?php if($Callssql9to11result->num_rows!=0){echo number_format($Connectssql9to11result->num_rows / $Callssql9to11result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Callssql11to12result->num_rows!=0){echo number_format($Connectsql11to12result->num_rows / $Callssql11to12result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Callssql12to13result->num_rows!=0){echo number_format($Connectssql12to13result->num_rows / $Callssql12to13result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Callssql13to14result->num_rows!=0){echo number_format($Connectssql13to14result->num_rows / $Callssql13to14result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Callssql14to15result->num_rows!=0){echo number_format($Connectssql14to15result->num_rows / $Callssql14to15result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Callssql15to16result->num_rows!=0){echo number_format($Connectssql15to16result->num_rows / $Callssql15to16result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Callssql16to17result->num_rows!=0){echo number_format($Connectssql16to17result->num_rows / $Callssql16to17result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Callssql17to18result->num_rows!=0){echo number_format($Connectssql17to18result->num_rows / $Callssql17to18result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Callssql18to20result->num_rows!=0){echo number_format($Connectssql18to20result->num_rows / $Callssql18to20result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($CallssqlALLresult->num_rows!=0){echo number_format($ConnectssqlALLresult->num_rows / $CallssqlALLresult->num_rows * 100, 2);}else{echo "0";} ?>%</td>
    </tr>
    <tr>
        <th scope="row">Connect</th>
        <td><?php echo $Connectssql9to11result->num_rows; ?></td>
        <td><?php echo $Connectsql11to12result->num_rows; ?></td>
        <td><?php echo $Connectssql12to13result->num_rows; ?></td>
        <td><?php echo $Connectssql13to14result->num_rows; ?></td>
        <td><?php echo $Connectssql14to15result->num_rows; ?></td>
        <td><?php echo $Connectssql15to16result->num_rows; ?></td>
        <td><?php echo $Connectssql16to17result->num_rows; ?></td>
        <td><?php echo $Connectssql17to18result->num_rows; ?></td>
        <td><?php echo $Connectssql18to20result->num_rows; ?></td>
        <td><?php echo $ConnectssqlALLresult->num_rows; ?></td>
    </tr>
    <tr>
        <th scope="row">Conv%</th>
        <td><?php if($Leadssql9to11result->num_rows!=0){echo number_format($Leadssql9to11result->num_rows / $Connectssql9to11result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Leadsql11to12result->num_rows!=0){echo number_format($Leadsql11to12result->num_rows / $Connectsql11to12result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Leadssql12to13result->num_rows!=0){echo number_format($Leadssql12to13result->num_rows / $Connectssql12to13result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Leadssql13to14result->num_rows!=0){echo number_format($Leadssql13to14result->num_rows / $Connectssql13to14result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Leadssql14to15result->num_rows!=0){echo number_format($Leadssql14to15result->num_rows / $Connectssql14to15result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Leadssql15to16result->num_rows!=0){echo number_format($Leadssql15to16result->num_rows / $Connectssql15to16result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Leadssql16to17result->num_rows!=0){echo number_format($Leadssql16to17result->num_rows / $Connectssql16to17result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Leadssql17to18result->num_rows!=0){echo number_format($Leadssql17to18result->num_rows / $Connectssql17to18result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($Leadssql18to20result->num_rows!=0){echo number_format($Leadssql18to20result->num_rows / $Connectssql18to20result->num_rows * 100, 2);}else{echo "0";} ?>%</td>
        <td><?php if($LeadssqlALLresult->num_rows!=0){echo number_format($LeadssqlALLresult->num_rows / $ConnectssqlALLresult->num_rows * 100, 2);}else{echo "0";} ?>%</td>
    </tr>
    <tr>
        <th scope="row">Leads</th>
        <td><?php echo $Leadssql9to11result->num_rows; ?></td>
        <td><?php echo $Leadsql11to12result->num_rows; ?></td>
        <td><?php echo $Leadssql12to13result->num_rows; ?></td>
        <td><?php echo $Leadssql13to14result->num_rows; ?></td>
        <td><?php echo $Leadssql14to15result->num_rows; ?></td>
        <td><?php echo $Leadssql15to16result->num_rows; ?></td>
        <td><?php echo $Leadssql16to17result->num_rows; ?></td>
        <td><?php echo $Leadssql17to18result->num_rows; ?></td>
        <td><?php echo $Leadssql18to20result->num_rows; ?></td>
        <td><?php echo $LeadssqlALLresult->num_rows; ?></td>
    </tr>
    <tr>
        <th scope="row">Active Agents</th>
        <td><?php echo $Agentssql9to11result->num_rows; ?></td>
        <td><?php echo $Agentsql11to12result->num_rows; ?></td>
        <td><?php echo $Agentssql12to13result->num_rows; ?></td>
        <td><?php echo $Agentssql13to14result->num_rows; ?></td>
        <td><?php echo $Agentssql14to15result->num_rows; ?></td>
        <td><?php echo $Agentssql15to16result->num_rows; ?></td>
        <td><?php echo $Agentssql16to17result->num_rows; ?></td>
        <td><?php echo $Agentssql17to18result->num_rows; ?></td>
        <td><?php echo $Agentssql18to20result->num_rows; ?></td>
        <td><?php echo $totalAgents; ?></td>
    </tr>
    <tr>
        <th scope="row">Calls Per Agent</th>
        <td><?php if($Callssql9to11result->num_rows!=0){echo number_format($Callssql9to11result->num_rows / $Agentssql9to11result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Callssql11to12result->num_rows!=0){echo number_format($Callssql11to12result->num_rows / $Agentsql11to12result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Callssql12to13result->num_rows!=0){echo number_format($Callssql12to13result->num_rows / $Agentssql12to13result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Callssql13to14result->num_rows!=0){echo number_format($Callssql13to14result->num_rows / $Agentssql13to14result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Callssql14to15result->num_rows!=0){echo number_format($Callssql14to15result->num_rows / $Agentssql14to15result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Callssql15to16result->num_rows!=0){echo number_format($Callssql15to16result->num_rows / $Agentssql15to16result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Callssql16to17result->num_rows!=0){echo number_format($Callssql16to17result->num_rows / $Agentssql16to17result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Callssql17to18result->num_rows!=0){echo number_format($Callssql17to18result->num_rows / $Agentssql17to18result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Callssql18to20result->num_rows!=0){echo number_format($Callssql18to20result->num_rows / $Agentssql18to20result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($CallssqlALLresult->num_rows!=0){echo number_format($CallssqlALLresult->num_rows / $totalAgents, 2);}else{echo "0";} ?></td>
    </tr>
    <tr>
        <th scope="row">Connects Per Agent</th>
        <td><?php if($Connectssql9to11result->num_rows!=0){echo number_format($Connectssql9to11result->num_rows / $Agentssql9to11result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Connectsql11to12result->num_rows!=0){echo number_format($Connectsql11to12result->num_rows / $Agentsql11to12result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Connectssql12to13result->num_rows!=0){echo number_format($Connectssql12to13result->num_rows / $Agentssql12to13result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Connectssql13to14result->num_rows!=0){echo number_format($Connectssql13to14result->num_rows / $Agentssql13to14result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Connectssql14to15result->num_rows!=0){echo number_format($Connectssql14to15result->num_rows / $Agentssql14to15result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Connectssql15to16result->num_rows!=0){echo number_format($Connectssql15to16result->num_rows / $Agentssql15to16result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Connectssql16to17result->num_rows!=0){echo number_format($Connectssql16to17result->num_rows / $Agentssql16to17result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Connectssql17to18result->num_rows!=0){echo number_format($Connectssql17to18result->num_rows / $Agentssql17to18result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Connectssql18to20result->num_rows!=0){echo number_format($Connectssql18to20result->num_rows / $Agentssql18to20result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($ConnectssqlALLresult->num_rows!=0){echo number_format($ConnectssqlALLresult->num_rows / $totalAgents, 2);}else{echo "0";} ?></td>
    </tr>
    <tr>
        <th scope="row">Leads Per Agent</th>
        <td><?php if($Leadssql9to11result->num_rows!=0){echo number_format($Leadssql9to11result->num_rows / $Agentssql9to11result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Leadsql11to12result->num_rows!=0){echo number_format($Leadsql11to12result->num_rows / $Agentsql11to12result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Leadssql12to13result->num_rows!=0){echo number_format($Leadssql12to13result->num_rows / $Agentssql12to13result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Leadssql13to14result->num_rows!=0){echo number_format($Leadssql13to14result->num_rows / $Agentssql13to14result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Leadssql14to15result->num_rows!=0){echo number_format($Leadssql14to15result->num_rows / $Agentssql14to15result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Leadssql15to16result->num_rows!=0){echo number_format($Leadssql15to16result->num_rows / $Agentssql15to16result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Leadssql16to17result->num_rows!=0){echo number_format($Leadssql16to17result->num_rows / $Agentssql16to17result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Leadssql17to18result->num_rows!=0){echo number_format($Leadssql17to18result->num_rows / $Agentssql17to18result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($Leadssql18to20result->num_rows!=0){echo number_format($Leadssql18to20result->num_rows / $Agentssql18to20result->num_rows, 2);}else{echo "0";} ?></td>
        <td><?php if($LeadssqlALLresult->num_rows!=0){echo number_format($LeadssqlALLresult->num_rows / $totalAgents, 2);}else{echo "0";} ?></td>
    </tr>
</table>





    9 - 11  11 - 12 12 - 13 13 - 14 14 - 15 15 - 16 16 - 17 17 - 18 18 - 19 Total
Calls   2677    6444    5865    72  0   0   0   0   0   15057
Connect%    5.83%   3.26%   2.59%   8.33%   0%  0%  0%  0%  0%  3.48%
Connect 156 210 152 6   0   0   0   0   0   524
Conv%   11.54%  9.05%   8.55%   0%  0%  0%  0%  0%  0%  9.54%
Leads   18  19  13  0   0   0   0   0   0   50
Active Agents   19  25  22  7   0   0   0   0   0   18.25
Calls Per Agent 140.89  257.76  266.59  10.29   0   0   0   0   0   825.04
Connects Per Agent  8.21    8.40    6.91    0.86    0   0   0   0   0   28.71
Leads Per Agent 0.95    0.76    0.59    0   0   0   0   0   0   2.74
php
mysql
database
time
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-04-04 13:50:54

不需要每小时运行一个查询。

代码语言：javascript
复制
SELECT call_date, COUNT(call_date) FROM `outbound_log_today` WHERE `campaign_id`='003'
GROUP BY call_date`

如果我得到正确的表结构，这个查询将给出每个call_date的行数。如果您将每个call_date循环到发生的时间，而不是精确的时间戳，它就会起作用。

如果您有时间戳，您可以：

代码语言：javascript
复制
SELECT HOUR(call_date), COUNT(call_date) FROM `outbound_log_today` WHERE `campaign_id`='003'
GROUP BY HOUR(call_date)`

HOUR()将从您的时间中提取出小时部分。也就是说，如果你有09:03:00，它会给你09。

每次运行此查询时，您都会得到运行该查询的每小时有多少行的计数。每次加载页面时，表中都会填充更新的值。然后，根据“调用日期”列，可以相应地填充每个单元格。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/55516105

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档