Gridview, InnoDB & Select Count(*)

Yii 2.0 General Discussions
1

Hello there,

I’m facing a pronounced time delay when I try to show a Gridview with a table that has more than 1 MM rows.

The table is a typical InnoDB with 7 to 10 columns, a PK, and a few FKs; when it has a few rows (less than 10K), all goes fast and neat.

There are a few articles mentioning the problem with InnoDB and its annoying table-scan in order to fetch the total (or approximated) number of rows.

Has Yii2 a workaround to moderate this issue?

Thank you,

S.

2

Is there any way I can change the computation of total records of the Gridview based on a modification to implement something like this:

SELECT table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = <database> and TABLE_NAME = <table_with_too_much_records>;

3

A Gridview (and also a Listview) uses the data provider’s getTotalCount() method to get the total number of records.

BaseDataProvider::getTotalCount()

And it calls BaseDataProvider::prepareTotalCount()

This is an abstract method and is implemented in ActiveDataProvider::prepareTotalCount()

This must be where you may want to customise, but …

As you may see, this method clones the main query and reset its limit, offset and order to get the total count. Note that it keeps the where conditions untouched, because you want to know the total count of records that match the given conditions.

4

You can set totalCount directly on ActiveDataProvider object, this will skip default count query, so you will be able to use any query (or even hardcoded/cached value) to calculate this value. But this may be tricky if you involve any kind of filtering in this view, since number of filtered records may differ from data in INFORMATION_SCHEMA.

1 Like
5

Thank you @softark & @rob006 !!

For the records, I implemented the following in the method search() at the searchs/model:

	$query = <Model>::find();

	$dataProvider = new ActiveDataProvider([
		'query' => $query,
	]);

	$sort = $dataProvider->getSort();
	$sort->defaultOrder = [ 'col1' => SORT_DESC ];

	// There are no filters, so we inject the total rows
	if( !( $this->load($params) && $this->validate() ) )
	{
		$connection = Yii::$app->getDb();

		// I know this is ugly...
		$sql = "Select table_rows From INFORMATION_SCHEMA.TABLES Where TABLE_SCHEMA = <schema> And TABLE_NAME = <tablename>";
		
		$dataProvider->setTotalCount( $connection->createCommand( $sql )->queryScalar() );
	}
	else
	{
		$query->andFilterWhere( [ '=', <col2>, $this->col2 ] )
			  ->andFilterWhere( [ 'like', <col3>, $this->col3 ] );
	}

return $dataProvider;

With best regards,

S.

1 Like