mysql - 与 MYSQL 失去连接

Question

我为 Codeigniter 编写了一个带有临时表的查询。它返回近 25000 行。但是当我在实时服务器上使用此查询时，它会丢失数据库连接。

这是我的查询:

SELECT temp.id office_id, 
       temp.office_name, 
       temp.svcic,
       count(service_transaction.service_id) AS service_total 
  FROM (SELECT users.id, users.office_name, service_list.id svcic 
          FROM users,service_list 
         WHERE users.del_status = 0 
           AND users.type='agency' 
           AND service_list.del_status = 0 
           AND users.id != '-1' ) temp 
     LEFT JOIN service ON temp.id = service.office_id 
           AND service.del_status=0 
           AND (service.date 
       BETWEEN '2017-07-26' 
           AND '2017-08-26' ) 
     LEFT JOIN service_transaction ON service_transaction.service_id = temp.svcic 
           AND service_transaction.service_transaction_id=service.id 
     LEFT JOIN recipient ON recipient.id =service.recipient_number 
           AND recipient.gender != 'all' 
      GROUP BY temp.id, 
               temp.office_name, 
               temp.svcic 
      ORDER BY temp.office_name

如何在不使用 temp 的情况下简化查询。我相信临时表发生了这种情况。

Answer 1

据我了解，原因之一可能是

innodb_buffer_pool_size 可以设置为大于服务器上可用的 RAM 大小。修复方法可能是使用 innodb_buffer_pool_size 的正确设置更新 my.cnf。