Query is taking to much time to execute?

Question

I am trying to get all the Users in a template but the below function and query is taking forever to load! How can i optimized the below function and query? The query almost takes 1 minute to load the users if the users are 6k+ in $search_params parameter.

Let say search parameter is:

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

Query:

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

If there is anything else you need i will provide!

Thanks

Answer 1

As you have a lot of joins and even more condition on fields which are very likely not indexed, the query can take its time. If you have a lot of data 1 Minute seems normal to me.

You can try adding a table you may call user_index in which you just put all those columns you want to query from all the tables you want to join (lets call them source tables). And every time you add/update/remove a row in any of those source tables you have to update the user_index too. If its not that neccesary that your user_index is always accurate you can also create a cron job which updates the user_index let's say every 5 minutes. Using this method your query will be a lot faster, if you update your user_index on every change of the source tables the save operations will be a little slower though.

Also check if the fields you have in your conditions are indexed correctly, the same accounts for the user_index table.