CGridView Autogenerated SQL

Yii 1.1 General Discussion
1

There is a query being generated that is killing my search and causing an Internal Error after I added approx 70K records to a few relational tables. The query is listed below and any assistance would be greatly appreciated.

I tested the page with only adding 10k entries and after a slow load would eventually render, 70K+ and I get an internal error.

SELECT COUNT(DISTINCT t.id)

FROM tbl_user t

LEFT OUTER JOIN tbl_states state ON (t.state_id=state.id)

LEFT OUTER JOIN tbl_profile profile ON (profile.user_id=t.id)

LEFT OUTER JOIN tbl_account_type accountType ON (t.account_type_id=accountType.id)

WHERE ((t.account_type_id=6) AND (t.status=1))

±—±------------±------------±-------±--------------±--------±--------±-----------------------------------±------±------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

±—±------------±------------±-------±--------------±--------±--------±-----------------------------------±------±------------+

| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 71067 | Using where |

| 1 | SIMPLE | state | eq_ref | PRIMARY | PRIMARY | 8 | manager_application_dev.t.state_id | 1 | Using index |

| 1 | SIMPLE | profile | ALL | NULL | NULL | NULL | NULL | 70588 | |

| 1 | SIMPLE | accountType | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |

±—±------------±------------±-------±--------------±--------±--------±-----------------------------------±------±------------+

2

I had a similar issue.

The query runs too slow.

Any ideas how to work around it?

Thanks!

3

Possible actions:

[list=1][][size=“2”]Add indexes: Index for ‘profile’ on the user_id, and and index for user on “account_type,status” or “status, account_type”.[/size][][size=“2”]Use INNER JOIN if allowed to avoid effect of null values.

(results would be different if user_id or account_type_id have null values). With an outer join, null values match anything so the result table could be 70kx70k in count.[/size][*][size="2"]Use lazy loading.

In this query the related tables do not have a search condition. With lazy loading only the needed related records would be loaded.[/size][*]Use KeenActiveDataProvider.

Better than lazy loading as it will fetch the related records with a minimum amount of queries.

-> loads selected users first and then the related records. This way you still kind of have the outer join and no join is needed in the initial query.[*]Use the RelatedSearchBehavior extension. This is particularly usefull with gridviews. It also uses KeenActiveDataProvider.

The demo shows how you can configure keen loading in "views/site/invoices.php":


$dataProvider=$model->search();

if($dataProvider instanceof KeenActiveDataProvider) {

    /* @var $dataProvider KeenActiveDataProvider */

    $dataProvider->withKeenLoading=array(

            'customer',

            'customer.support',

            'invoicelines.track.genre',

    );

}

[/list]