Why is ActiveDataProvider executing COUNT(*) query

ActiveDataProvider is executing COUNT(*) query that takes too long to execute.

Why is it executing this query, and can I turn that off somehow ?

Here is my code:

$query = Document::find();


// build dynamic conditions for document table

$this->buildDocumentQuery($query, $params);


$query->joinWith('documentContent');

// build dynamic conditions for document_content table

$this->buildDocumentContentQuery($query, $params);


// add conditions that should always apply here

$dataProvider = new ActiveDataProvider([

    'query' => $query,

    'sort'  => ['defaultOrder' => ['id' => SORT_DESC]],

    'pagination' => [

        'pageSize' => 10,

    ],

]);


return $dataProvider;

And here is the query executed:

SELECT COUNT(*) FROM `document` LEFT JOIN `document_content` ON `document`.`id` = `document_content`.`document_id`

That query is needed to create pagination.

Have you tried to set INDEX on document.id and document_content.document_id database table column ?

There are indexes there. Anyway if I turn off pagination I get even worse performance.

Here is what else I have tried. I have turned off pagination. And I have set limit to 500 documents, because without limit php runs out of memory. So yii execute these 2 queries:

SELECT `document`.* FROM `document` LEFT JOIN `document_content` ON `document`.`id` = `document_content`.`document_id` ORDER BY `id` DESC LIMIT 500

SELECT * FROM `document_content` WHERE `document_id` IN (500 IDs)

What do you think, would I get better performance if I execute one JOIN query that will get data from both tables ? Like this:

SELECT * FROM `document` LEFT JOIN `document_content` ON `document`.`id` = `document_content`.`document_id` ORDER BY `document`.`id` DESC LIMIT 500

It seems to me that this query is running faster, but I am not sure. I don’t know how to make/execute it so that ActiveDataProvider can use it. Then I can see in profiler if it is better.