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
// build dynamic conditions for document_content table
// add conditions that should always apply here
$dataProvider = new ActiveDataProvider([
'query' => $query,
'sort' => ['defaultOrder' => ['id' => SORT_DESC]],
'pagination' => [
'pageSize' => 10,
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.