php - 如何拆分 ActiveRecord 查询以提高速度

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 服务器都会创建大型临时表(连接过程)。尝试检查索引，如果它们使用正确，在数据库中创建 View 并询问您需要从中得到什么。