CSqlDataProvider pagination method

Hi guys,

$count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM tbl_user')->queryScalar();

$sql='SELECT * FROM user';

$dataProvider=new CSqlDataProvider($sql, array(




             'id', 'username', 'email',







Let say my db usercontains 10,000 records.

it will query all the record and fetch it out in 1 times when i set pagination-pageSize=10,right ??

mean each pages contain 10 records, total pages is 1000.

If like that, will it eat a lot of my processing time??

Any solution to improve the processing time?

It will only query the 10 records for the current page, not more. Quote from the class reference:

Hi Mike

It will only query the 10 records for the current page, not more.

So given case which one is the concept for pagination-pageSize=10.


It only query 10 records for the current page

  1. directly stop query

  2. will show records from 11-20 when user click 2nd pages?

pagination-pageSize=10 izzit same concept with sql set limit(10)??

In sql set limit(10)will only fetch the latest 10 records while pagination-pageSize=10 will set each page with 10 records till the end of records? right?

Pagination is based on the LIMIT clause in SQL. LIMT is not only to limit the row count of your result set, it also can be used to define an offset: "LIMIT offset, row_count". So: Yes, it will always only fetch the 10 items of the page you currently see. So if you load page 3, it will only read the records from 31-40 from your DB.

Hi Mike,

Thanks for your description… =)