Как улучшить производительность запросов в Symfony?

Приведенный ниже запрос занимает слишком много времени для загрузки данных. Что-то не так? Что я могу улучшить здесь?

Приведенная ниже функция извлекает все записи пользователей с некоторыми объединениями между диапазонами дат, введенными пользователем.

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
";

Выше сырой запрос. $startdate а также $enddate переменные вводятся пользователями и могут начинаться с 2000 года по сегодняшний день. Я надеюсь, что мой вопрос ясен. Если есть путаница, просто дайте мне знать, я обновлю свой вопрос. Если требуются какие-либо другие данные, просто прокомментируйте, и я поделюсь ими быстро. Давайте посмотрим, куда это идет. Заранее спасибо.

1

Решение

Задача ещё не решена.

Другие решения

Других решений пока нет …

По вопросам рекламы [email protected]