Hi!
Basically I have one ActiveRecord that has relation inside of it.
public function relations()
{
return array_merge(
parent::relations(),
array(
'linkCount' => array(self::STAT, 'StatisticClicksLink', 'group_id'),
)
);
}
Which then generates this query
SELECT `t`.`group_id` AS `c`, COUNT(*) AS `s` FROM `statistic_clicks_link` `t` WHERE (`t`.`group_id`='665') GROUP BY `t`.`group_id`
and explain gives this:
mysql> EXPLAIN SELECT `t`.`group_id` AS `c`, COUNT(*) AS `s` FROM `statistic_clicks_link` `t` WHERE (`t`.`group_id`='665') GROUP BY `t`.`group_id`;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------------------------------------+
| 1 | SIMPLE | t | ref | group_id | group_id | 4 | const | 3 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------------------------------------+
but if i change the group_id ‘665’ to 665 then this is what EXPLAIN gives:
mysql> EXPLAIN SELECT `t`.`group_id` AS `c`, COUNT(*) AS `s` FROM `statistic_clicks_link` `t` WHERE (`t`.`group_id`=665) GROUP BY `t`.`group_id`;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | t | ref | group_id | group_id | 4 | const | 3 | Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
I think that’s a huge difference between
Using where; Using index; Using temporary; Using filesort
and
Using index
statistic_clicks_link table:
CREATE TABLE `statistic_clicks_link` (
`id` INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
`group_id` INT(10) UNSIGNED NOT NULL,
`hash` VARCHAR(32) NULL DEFAULT NULL COLLATE 'utf8_bin',
`title` VARCHAR(256) NOT NULL,
`link` VARCHAR(2048) NOT NULL,
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`http_code` INT(3) UNSIGNED NOT NULL DEFAULT '301',
PRIMARY KEY (`id`),
INDEX `group_id` (`group_id`, `link`(255)),
INDEX `U_hash` (`hash`) USING BTREE,
CONSTRAINT `FK_statistic_clicks_link_group_id` FOREIGN KEY (`group_id`) REFERENCES `statistic_clicks_group` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
So I am wondering if there is this one case then how many other cases are there that have such difference.
How to tell Yii to set parameter according to the type in database not the way thats easier for Yii (all strings)?
MySQL 5.5.31
Yii 1.1.14
)