执行查询需要很长时间

Question

我正在尝试在模板中获取所有用户，但下面的函数和查询需要很长时间才能加载！如何优化以下函数和查询？如果用户在 $search_params 参数中为 6k+，则查询几乎需要 1 分钟来加载用户。

假设搜索参数为：

  $searchParams = array('start_date' => date('Y-m-d', strtotime('-2 days')) , 'end_date' => date("Y-m-d"));

查询：

public static function getBtqUsers($search_params) {
    $criteria = new Criteria();
    $criteria->clearSelectColumns();
    $criteria->addSelectColumn("btq_user.id as id");
    $criteria->addSelectColumn("btq_user.drid as drid");
    $criteria->addSelectColumn("btq_user.name as name");
    $criteria->addSelectColumn("btq_user.email as email");
    $criteria->addSelectColumn("btq_user.active as active");
    $criteria->addSelectColumn("btq_user.lead_from as lead_from");
    $criteria->addSelectColumn("btq_user.source as source");
    $criteria->addSelectColumn("btq_user.http_referer as http_referer");
    $criteria->addSelectColumn("btq_user.sms_status as sms_status");
    $criteria->addSelectColumn("btq_user.telefon as telefon");
    $criteria->addSelectColumn("btq_user.datain as datain");
    $criteria->addSelectColumn("btq_user.kall as kall");
    $criteria->addSelectColumn("btq_user.ip_address as ip_address");
    $criteria->addSelectColumn("count(btq_user_track_blog_video.counter) as total_video_viewed");
    $criteria->addSelectColumn("btq_doctors.id as dr_id");
    $criteria->addSelectColumn("btq_doctors.drname as dr_name");
    $criteria->addSelectColumn("btq_doctors.drphone as dr_phone");
    $criteria->addSelectColumn("btq_doctors.initials as dr_initials");
    $criteria->addSelectColumn("btq_doctors.drmail as dr_mail");
    $criteria->addSelectColumn("event_data.btq_user_id as event_user_id");
    $criteria->addSelectColumn("count(if(event_data.event_parent_id=2, event_data.event_parent_id, null)) as kms_total_video_viewed");
    $criteria->addSelectColumn("count(if(event_data.event_parent_id=1, event_data.event_parent_id, null)) as kms_total_blog_viewed");
    $criteria->addSelectColumn("state.pres as pres");
    $criteria->addSelectColumn("state.state as state");
    $criteria->addSelectColumn("btq_user_sales_choice.type as choice_type");
    $criteria->addSelectColumn("btq_user_sales_choice.opt_value as choice_value");
    $criteria->addSelectColumn("btq_user_sales_choice.opt_text as choice_text");
    $criteria->addSelectColumn("btq_user.pfu_customer_id as pfu_customer_id");
    $criteria->addSelectColumn("lead_schedule.id as schedule_id");
    $criteria->addSelectColumn("lead_schedule.created_at as schedule_date");
    $criteria->addJoin(self::STATE_ID, StatePeer::ID, Criteria::LEFT_JOIN);
    $criteria->addJoin(self::BTQ_USER_SALES_CHOICE_ID, BtqUserSalesChoicePeer::ID, Criteria::LEFT_JOIN);
    $criteria->addJoin(self::ID, LeadSchedulePeer::LEAD_ID, Criteria::LEFT_JOIN);
    $criteria->addJoin(self::DRID, BtqDoctorsPeer::ID, Criteria::LEFT_JOIN);
    $criteria->addJoin(self::ID, BtqUserTrackBlogVideoPeer::USER_ID, Criteria::LEFT_JOIN);
//    $criteria->addJoin(self::DRID, BtqDoctorPfuAdminPeer::BTQ_DOCTOR_ID, Criteria::LEFT_JOIN);
//    $criteria->addExtraJoin(BtqDoctorPfuAdminPeer::PFU_ADMIN_ID, PfuCustomerPeer::PFU_ADMIN_ID, Criteria::LEFT_JOIN, " AND " . PfuCustomerPeer::EMAIL . " = " . self::EMAIL);
    $criteria->addJoin(self::ID, EventDataPeer::BTQ_USER_ID, Criteria::LEFT_JOIN);
    //$criteria->addGroupByColumn(self::ID);
    $criteria->addGroupByColumn(self::EMAIL);
    //$criteria->addGroupByColumn(EventDataPeer::BTQ_USER_ID);
    $criteria->add(BtqUserPeer::IS_DUMMY_DETAIL, "1", Criteria::NOT_EQUAL);
    $criteria->addDescendingOrderByColumn(self::DATAIN);
    if (!empty($search_params)) {
      foreach ($search_params as $key => $param) {
        if (trim($param)) {
          $param = addslashes($param);
          switch ($key) {
            case 'name':
              $criteria->add(BtqUserPeer::NAME, "%" . $param . "%", Criteria::LIKE);
              break;
            case 'email':
              $criteria->add(BtqUserPeer::EMAIL, "%" . $param . "%", Criteria::LIKE);
              break;
            case 'dr_name':
              $criteria->add(BtqDoctorsPeer::DRNAME, "%" . $param . "%", Criteria::LIKE);
              break;
            case 'phone':
              $criteria->add(BtqUserPeer::TELEFON, "%" . $param . "%", Criteria::LIKE);
              break;
            case 'lead_from':
              $criteria->add(BtqUserPeer::LEAD_FROM, $param, Criteria::EQUAL);
              break;
            case 'start_date':
              $criteria->add(BtqUserPeer::DATAIN, $param . " 00:00:00", Criteria::GREATER_EQUAL);
              break;
            case 'end_date':
              $criteria->addAnd(BtqUserPeer::DATAIN, $param . " 23:59:59", Criteria::LESS_EQUAL);
              break;
            case 'location':
              if ($param == "Local")
                $criteria->add(StatePeer::PRES, array("MD", "VA", "DC"), Criteria::IN);
              else if ($param == "Non Local")
                $criteria->add(StatePeer::PRES, array("MD", "VA", "DC"), Criteria::NOT_IN);
              break;
          }
        }
      }
    }//FB::log($criteria);
    return $criteria;
//    return self::doSelectStmt($criteria)->fetchAll();
  }

如果您还需要什么，我会提供！

谢谢

Answer 1

由于您有很多联接，并且很可能未编制索引的字段具有更多条件，因此查询可能需要一些时间。如果你有很多数据，1分钟对我来说似乎很正常。

您可以尝试添加一个可以调用的表user_index其中只需从要连接的所有表中放置要查询的所有列(我们称它们为源表(。每次添加/更新/删除任何这些源表中的行时，您也必须更新user_index。如果您的user_index始终准确，您还可以创建一个 cron 作业，该作业每 5 分钟更新一次user_index。使用此方法，您的查询会快得多，如果您在源表的每次更改时更新user_index，则保存操作会慢一些。

还要检查条件中的字段是否已正确编制索引，与user_index表的帐户相同。