Anybody knows why select count(*) is always executed everytime CGridView is used?

I have 2 tables…

table 1: order_details

columns: order_id, product, subtotal

table 2: order

columns: id, order_type, total, user_id

code on order_details model:

public function relations()


		return array(

               'orders' => array(self::BELONGS_TO,'orders','order_id'),




code on orders model:

public function relations()


		return array(

               'order_details' => array(self::HAS_ONE,'order_details','order_id'),



i get the correct sql statement:

SELECT `od`.`order_id` AS `t0_c1`, `od`.`sld` AS `t0_c2`,

`od`.`subtotal` AS `t0_c5`, `od`.`id` AS `t0_c0`, `o`.`order_type` AS

`t1_c1`, `o`.`total` AS `t1_c11`, `o`.`user_id` AS `t1_c3`, `o`.`time` AS

`t1_c10`, `o`.`status` AS `t1_c4`, `o`.`rcn_country` AS `t1_c20`, `o`.`id`

AS `t1_c0` FROM `order_details` `od`  INNER JOIN `orders` `o` ON

(`od`.`order_id`=`o`.`id`) LIMIT 20

but before that, i also see this sql query, which is very slow to execute:

SELECT COUNT(DISTINCT `od`.`id`) FROM `order_details` `od` 

INNER JOIN `orders` `o` ON (`od`.`order_id`=`o`.`id`)

What is the use of the select count() query? Or any ideas to optimize?

The pager needs to know the total number of hits. You can’t get a result page + total hits with a single query.

Ok. I see.

I have 500,000 records counted by that query… Any ideas to optimize it? it takes 7 seconds for the select count(*) with join statements to execute.

What do i do to skip the join clause?

Hmm. Seems a bit long. I guess you already have indexes defined in your DB for o.order_id?

count(1) instead of count(*) won’t solve the problem at all, but it helps when performance is a requirement



Maybe, but you can’t influence what SQL is created for the count query. I wonder if another property for CDbCriteria would make sense:

// SQL expression to use for count() queries:

$criteria->count='COUNT(*)';  // default

$criteria->count='COUNT(DISTINCT t.ID)'; // default with distinct=true

$criteria->count='COUNT(1)';  // customized