Performance improvement - Yii2 Eager loading

Question

We have a table called content and size of table is over 5Gb. Our Yii2 backend application has a section to view those content and there are few filters available to filter out contents. I need to filter contents which are assigned or not assigned to categories.

Relations

A Content can be shared within many categories. So there is a one to many relationship between Content and Category tables.

Mapping table is content_cat_xref.

Models are as follows.

Content model

 /**
 * @return \yii\db\ActiveQuery
 */
public function getContentCatXrefs()
{
    return $this->hasMany(ContentCatXref::className(), ['content_id' => 'id']);
}

Content_cat_xref model

/**
 * @return \yii\db\ActiveQuery
 */
public function getCategory()
{
    return $this->hasOne(Category::className(), ['id' => 'category_id']);
}

/**
 * @return \yii\db\ActiveQuery
 */
public function getContent()
{
    return $this->hasOne(Content::className(), ['id' => 'content_id']);
}

Controller

 public function actionIndex()
{
    $searchModel = new \common\models\ContentSearch();
    $dataProvider = $searchModel->searchMedia(Yii::$app->request->queryParams);
    return $this->render('index', [
        'searchModel' => $searchModel,
        'dataProvider' => $dataProvider
    ]); 
}

ContentSearch Model

public function searchMedia($params)
{
    $query = Content::find()->where(['file_type_id'=>[1,2,3,4,5,6,8,11]]);
    
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);
    
    $this->load($params);
    
    if (!$this->validate()) {
        return $dataProvider;
    }
    
    //Conditionally add filters
    if(isset($this->is_category_available) && $this->is_category_available === 'yes'){
        $query = $query->joinWith(['contentCatXrefs' => function($queryw){
            $queryw->andWhere(['is not', 'content_cat_xref.category_id', new \yii\db\Expression('null')]);
        }]);
    }else if(isset($this->is_category_available) && $this->is_category_available === 'no'){
        $query = $query->joinWith(['contentCatXrefs' => function($queryw){
            $queryw->andWhere(['is', 'content_cat_xref.category_id', new \yii\db\Expression('null')]);
        }]);
    }
    
    if(!is_null($this->file_name) && !empty($this->file_name)) {
        $query->andWhere("MATCH (file_name) AGAINST (\"".$this->file_name."\" IN NATURAL LANGUAGE MODE)");
    }
    
    if(!is_null($this->file_path) && !empty($this->file_path)) {
        $query->andWhere("MATCH (file_path) AGAINST (\"".$this->file_path."\" IN NATURAL LANGUAGE MODE)");
    }
    
    $query->andFilterWhere(['type_code'=>$this->type_code]);
    $query->andFilterWhere(['file_type_id'=>$this->file_type_id]);
    $query->andFilterWhere(['content.active_status'=>$this->active_status]);
    $query->andFilterWhere(['content.content_status'=>$this->content_status]);
    
    if(is_null($this->file_name)) {
        $query->orderBy("id desc");
    }

    return $dataProvider;
}

I have few questions

1. What should be the correct and efficient way to join two tables( content and content_cat_xref) to retrieve data. I need to filter contents which are assigned or not assigned to categories. So, I use this filter for that.

       if(isset($this->is_category_available) && $this->is_category_available === 'yes'){
       $query = $query->joinWith(['contentCatXrefs' => function($queryw){
           $queryw->andWhere(['is not', 'content_cat_xref.category_id', new \yii\db\Expression('null')]);
       }]);
   }else if(isset($this->is_category_available) && $this->is_category_available === 'no'){
       $query = $query->joinWith(['contentCatXrefs' => function($queryw){
           $queryw->andWhere(['is', 'content_cat_xref.category_id', new \yii\db\Expression('null')]);
       }]);
   }
   

2. I use eager loading to improve performance here. But It gives duplicate records when joining these two tables. After I use distinct then it reduce the performance. What should be the correct way to do this Yii2?

3. Is there are way to pass params to relation functions when constructing query in content search model?

4. If we order content by id descending, again reduce the performance.

It is highly appreciated if there is anyone who can provide guidance on this.

Answer 1

1. If the goal is to filter contents which are assigned or not assigned to categories, I will suggest to maintain a flag in "content" table. Or you can add index in existing tables to improve the performance on join and conditions, but it will be always slower than the condition on a flag field on a single table. You can use "explain" statement to get the information about the indexes used nd also it will be helpful to you to add new index
2. Issue is not with Yii2 but will the records maintain in the tables. If you have multiple entries in "content_cat_xref" table for a respective row from "content" table, then your join will return multiple records, where records from "content" table will be same for respective row but records from "content_cat_xref" will be different for each row. Solution is to use subquery (will effect the performance) or maintain the flag as explained in point 1
3. In Yii2, relations are formed based on method return in model class. You can write conditions in that methods. e.g.:

if($this->is_category_available == 'yes') {
return $this->hasMany(ContentCatXref::className(), ['content_id' => 'id'])
        ->andOnCondition(['a_type' => 1]);
}
else  {
return $this->hasMany(ContentCatXref::className(), ['content_id' => 'id'])
        ->andOnCondition(['a_type' => 0]);
}

4. Add indexes in your table