The below query is taking too much time to load data. Is there anything wrong? What can i improve in here?
The below function is fetching all users records with some joins between date range input by user.
public static function getUsers($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::ID, EventDataPeer::BTQ_USER_ID, Criteria::LEFT_JOIN);
$criteria->add(BtqUserPeer::IS_DUMMY_DETAIL, "1", Criteria::NOT_EQUAL);
$criteria->addDescendingOrderByColumn(self::DATAIN);
$criteria->addGroupByColumn(self::EMAIL);
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, Criteria::GREATER_EQUAL);
break;
case 'end_date':
$criteria->addAnd(BtqUserPeer::DATAIN, $param, 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;
}
}
}
}
return $criteria;
}
RAW QUERY:
$startdate = $searchParams['start_date'];
$enddate = $searchParams['end_date'];
$connection = Propel::getConnection();
$query = "SELECT btq_user.id as id, btq_user.drid as drid, btq_user.name as name, btq_user.email as email, btq_user.active as active,
btq_user.lead_from as lead_from, btq_user.source as source, btq_user.http_referer as http_referer, btq_user.sms_status as sms_status,
btq_user.telefon as telefon, btq_user.datain as datain, btq_user.kall as kall, btq_user.ip_address as ip_address,
count(btq_user_track_blog_video.counter) as total_video_viewed, btq_doctors.id as dr_id, btq_doctors.drname as dr_name,
btq_doctors.drphone as dr_phone, btq_doctors.initials as dr_initials, btq_doctors.drmail as dr_mail, event_data.btq_user_id as event_user_id,
count(if(event_data.event_parent_id=2, event_data.event_parent_id, null)) as kms_total_video_viewed,
count(if(event_data.event_parent_id=1, event_data.event_parent_id, null)) as kms_total_blog_viewed,
state.pres as pres, state.state as state, btq_user_sales_choice.type as choice_type,
btq_user_sales_choice.opt_value as choice_value, btq_user_sales_choice.opt_text as choice_text, lead_schedule.id as schedule_id,
lead_schedule.created_at as schedule_date FROM `btq_user`
LEFT JOIN state ON (btq_user.STATE_ID=state.ID)
LEFT JOIN btq_user_sales_choice ON (btq_user.BTQ_USER_SALES_CHOICE_ID=btq_user_sales_choice.ID)
LEFT JOIN lead_schedule ON (btq_user.ID=lead_schedule.LEAD_ID)
LEFT JOIN btq_doctors ON (btq_user.DRID=btq_doctors.ID)
LEFT JOIN btq_user_track_blog_video ON (btq_user.ID=btq_user_track_blog_video.USER_ID)
LEFT JOIN event_data ON (btq_user.ID=event_data.BTQ_USER_ID)
WHERE btq_user.IS_DUMMY_DETAIL<>1 AND (btq_user.DATAIN>='$startdate' AND btq_user.DATAIN<='$enddate')
GROUP BY btq_user.EMAIL ORDER BY btq_user.DATAIN DESC LIMIT 20
";
The above is raw query. $startdate and $enddate variables are input by users it can start from 2000 to today's date. I hope my question is clear. If there is any confusion just let me know I will update my question. If any other data is required just comment and I will share it quickly. Let see where it goes. Thanks in advance.
