如何拆分活动记录查询以提高速度

Question

任何帮助都将不胜感激。

我有一个正在执行的活动记录查询，由于表中有许多记录，该查询的运行速度非常慢。

我正在考虑将函数拆分成几个单独的查询，然后将它们连接起来。虽然我不确定最好的方法。我目前正在搜索用户和个人资料表两次，所以我现在有一些可以改进的地方。

我已经附加了下面的activerecord查询，如果我没有弄清楚任何事情，请让我知道，我会编辑我的问题。

提前谢谢。

$this->db->select(
    'stage_one_application.id as application_id,
    stage_one_application.proposal_title as title, 
    stage_one_application.proposal_abstract, 
    category.faq_category_title as category,
    stage_one_application.proposal_category_alt 
        AS category_freetext, 
    stage_one_application.pooling as pool, 
    user.id as user_id, 
    profile.first_name, 
    profile.last_name, 
    profile.date_of_birth, 
    stage_one_application.admin_feasibility 
        AS feasibility_score, 
    stage_one_application.admin_innovation 
        AS innovation_score, 
    stage_one_application.admin_technical_excellence 
        AS technical_excellence_score, 
    stage_one_application.application_accepted 
        AS through_to_stage_two, 
    rev_profile.first_name AS reviewer_firstname, 
    rev_profile.last_name as reviewer_lastname, 
    application_stage_one_scoring.innovation 
        AS reviewer_score_innovation, 
    application_stage_one_scoring.feasibility 
        AS reviewer_score_feasibility, 
    application_stage_one_scoring.technical_excellence 
        AS reviewer_score_technical_excellence,
    application_stage_one_scoring.application_stage_one_reviewer_comment 
        AS reviewer_comment'
);
$this->db->from('stage_one_application');
$this->db->join('users as user', 'user.id = stage_one_application.candidate_id');
$this->db->join(
    'profiles as profile', 
    'profile.user_id = stage_one_application.candidate_id'
);
$this->db->join(
    'faq_categories as category', 
    'category.id = stage_one_application.proposal_category'
);
$this->db->join(
    'application_stage_one_scoring',
    'application_stage_one_scoring.application_stage_one_id = stage_one_application.id',
    'left'
);
$this->db->join(
    'users as reviewer', 
    'reviewer.id = application_stage_one_scoring.application_stage_one_scoring_reviewer'
);
$this->db->join(
'profiles as rev_profile', 
'rev_profile.user_id=application_stage_one_scoring.application_stage_one_scoring_reviewer'
);
$this->db->order_by("application_id", "desc"); 
$query = $this->db->get();

Answer 1

我认为这不是CodeIgniter的问题。您需要优化您的数据库，因为SQL-server在每次查询时都会创建大型临时表(连接过程)。尝试检查INDEXes，如果它们使用正确，则在数据库中创建视图，并询问您需要从其中获得什么。