Info block from the definitive guide:
Quote
I'm writing quite big portal now, so I'm very concerned about efficiency…
I'm using InnoDB tables (see mysql performance blog, it is faster, than MyISAM and I have tests for it)
That's why I was running some tests. Let's take the live example:
There is a site about clothes. There are categories (dresses, t-shirts etc), each category has size categories (dress sizes, generic sizes, UK sizes, european sizes etc), each size category holds sizes itself. For example category "generic sizes" holds S,M,L, XL, XXL.
See attached scheme.
Now running 2 tests in the actionIndex of the controller:
$t = microtime(true); $cmd = Yii::app()->db->createCommand('SELECT s.*, sc.* FROM Size s INNER JOIN SizeCategory sc ON s.sizeCategoryID=sc.sizeCategoryID INNER JOIN CategorySizeCategory csc ON csc.sizeCategoryID=sc.sizeCategoryID WHERE csc.categoryID=:cID'); $cmd->bindParam(':cID', $this->categoryID, PDO::PARAM_INT); $sizes = $cmd->queryAll(); $interval = microtime(true)-$t; $t = microtime(true); $category = Category::model()->with(array('sizeCategories' => array('sizes')))->findByPk($this->categoryID); $szs = $category->sizeCategories; $interval2 = microtime(true)-$t; $this->render('index', array('sizes' => $sizes, 'int' => $interval, 'sizes2' => $szs, 'int2' => $interval2));
First variant - low-level SQL pus binding params.
Second - using relational AR queries.
Here are relations for:
Category:
public function relations() { return array( 'sizeCategories'=>array(self::MANY_MANY, 'SizeCategory', 'CategorySizeCategory(categoryID, sizeCategoryID)', 'order' => 'sizeCategory'), 'products'=>array(self::HAS_MANY , 'Product', 'categoryID', 'order' => 'name'), 'tags'=>array(self::HAS_MANY , 'CategoryTag', 'categoryID', 'order' => '??.clicksNum', 'limit' => Yii::app()->params['maxCategoryTags']), ); }
SizeCategory:
public function relations() { return array( 'sizes'=>array(self::HAS_MANY, 'Size', 'sizeCategoryID', 'order'=>'size'), ); }
Here is DB structure for these tables:
-- ----------------------------------------------------- -- Table `everystyle`.`Category` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `everystyle`.`Category` ( `categoryID` INT(5) NOT NULL AUTO_INCREMENT , `name` VARCHAR(100) NOT NULL , `clicksNum` INT(11) NOT NULL DEFAULT 0 , `gender` ENUM('male','female','boys','girls') NOT NULL , PRIMARY KEY (`categoryID`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `everystyle`.`SizeCategory` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `everystyle`.`SizeCategory` ( `sizeCategoryID` INT(11) NOT NULL , `sizeCategory` VARCHAR(100) NULL , PRIMARY KEY (`sizeCategoryID`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `everystyle`.`Size` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `everystyle`.`Size` ( `sizeID` INT(11) NOT NULL AUTO_INCREMENT , `size` VARCHAR(45) NULL , `sizeCategoryID` INT(11) NOT NULL , PRIMARY KEY (`sizeID`) , INDEX `fk_Size_sizeCategory` (`sizeCategoryID` ASC) , CONSTRAINT `fk_Size_sizeCategory` FOREIGN KEY (`sizeCategoryID` ) REFERENCES `everystyle`.`SizeCategory` (`sizeCategoryID` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `everystyle`.`CategorySizeCategory` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `everystyle`.`CategorySizeCategory` ( `categoryID` INT(11) NOT NULL , `sizeCategoryID` INT(11) NOT NULL , INDEX `fk_CategorySizeCategory_Category` (`categoryID` ASC) , INDEX `fk_CategorySizeCategory_sizeCategory` (`sizeCategoryID` ASC) , PRIMARY KEY (`categoryID`, `sizeCategoryID`) , CONSTRAINT `fk_CategorySizeCategory_Category` FOREIGN KEY (`categoryID` ) REFERENCES `everystyle`.`Category` (`categoryID` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_CategorySizeCategory_sizeCategory` FOREIGN KEY (`sizeCategoryID` ) REFERENCES `everystyle`.`SizeCategory` (`sizeCategoryID` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
Results of test is:
0.00061607360839844 - SQL 0.028649091720581 - ActiveRecord
Active record is 46(!!!) times slower, than simple SQL!
Here is the secret of "efficiency" (stated in the definitive guide). While SQL query uses 1 query with 2 joins, relational query with active record uses 9 (!!!) queries. See application log:
2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SELECT s.*, sc.* FROM Size s INNER JOIN SizeCategory sc ON s.sizeCategoryID=sc.sizeCategoryID INNER JOIN CategorySizeCategory csc ON csc.sizeCategoryID=sc.sizeCategoryID WHERE csc.categoryID=:cID 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SHOW COLUMNS FROM `SizeCategory` 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SHOW CREATE TABLE `SizeCategory` 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SHOW COLUMNS FROM `Size` 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SHOW CREATE TABLE `Size` 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SELECT `Category`.`categoryID` AS t0_c0, `Category`.`intName` AS t0_c1, `Category`.`name` AS t0_c2, `Category`.`clicksNum` AS t0_c3, `Category`.`gender` AS t0_c4 FROM `Category` WHERE `Category`.`categoryID`=1 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SHOW COLUMNS FROM `CategorySizeCategory` 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SHOW CREATE TABLE `CategorySizeCategory` 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SELECT `Category`.`categoryID` AS t0_c0, t1.`sizeCategoryID` AS t1_c0, t1.`sizeCategory` AS t1_c1 FROM `Category` LEFT OUTER JOIN `CategorySizeCategory` sizeCategories_t1 ON `Category`.`categoryID`=sizeCategories_t1.`categoryID` LEFT OUTER JOIN `SizeCategory` t1 ON t1.`sizeCategoryID`=sizeCategories_t1.`sizeCategoryID` WHERE `Category`.`categoryID`=1 ORDER BY sizeCategory 2008/12/16 11:03:53 [trace] [system.db.CDbCommand] query with SQL: SELECT t1.`sizeCategoryID` AS t1_c0, t2.`sizeID` AS t2_c0, t2.`size` AS t2_c1, t2.`sizeCategoryID` AS t2_c2 FROM `SizeCategory` t1 LEFT OUTER JOIN `Size` t2 ON t2.`sizeCategoryID`=t1.`sizeCategoryID` WHERE t1.`sizeCategoryID` IN (2, 1) ORDER BY size
Any comments on this?