Hi everyone,
We just finished our first yii application and i’ve been assigned the optimization of our queries and databases.
One of the queries i’ve been having issues, is one produced by a self::STAT relation like the one bellow
public function relations()
{
return array(
'total_stages' => array(self::STAT, 'Stage', 'work_id'),
);
}
This is from the model Work, it is a simple stat relation and on model Stage which has a primary key ‘id’ and a ‘work_id’ foreign key relation, back to Work.
The query that this relation produces seems to be the following.
SELECT `work_id` AS `c`, COUNT(*) AS `s` FROM `stage` `t` WHERE (`t`.`work_id`='2') GROUP BY `work_id`
The issue comes from the GROUP BY statement, if you pass the query through mysql client’s describe command i get something like (notice the Extra column)
mysql> describe SELECT `work_id` AS `c`, COUNT(*) AS `s` FROM `stage` `t` WHERE (`t`.`work_id`='2') GROUP BY `work_id`;
+----+-------------+-------+------+-------------------+---------+---------+-------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+---------+---------+-------+------+-----------------------------------------------------------+
| 1 | SIMPLE | t | ref | work_id,work_id_2 | work_id | 4 | const | 3 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+------+-------------------+---------+---------+-------+------+-----------------------------------------------------------+
1 row in set (0.03 sec)
Notice that by simply removing the ‘GROUP BY’ statement i get far better results, something like.
mysql> describe SELECT `work_id` AS `c`, COUNT(*) AS `s` FROM `stage` `t` WHERE (`t`.`work_id`='2');
+----+-------------+-------+------+-------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | t | ref | work_id,work_id_2 | work_id | 4 | const | 3 | Using index |
+----+-------------+-------+------+-------------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)
So my question is,
Is there a way to stop the self::STAT from using ‘GROUP BY’ on some of the relations?
Thanks in advance
pantelis