Tony Thomas

When it comes to optimizing your CakePHP queries, you need to abandon Recursive and adopt Containable.

In the example below I have the following models:

• Patient
• Specimen
• Result
• ResultType

The associations in the model are:

• Result
  • belongsTo
    • ResultType
      • hasMany
        • Result
    • Patient
      • hasMany
        • Result
        • Specimen
    • Specimen
      • belongsTo
        • Patient
      • hasMany
        • Result

$paginate in the Result Controller

Original version

var $paginate = array(
		'fields' => array(
			'ResultType.type',
			'Specimen.draw_date',
			'Result.id',
			'Result.patient_id',
			'Result.specimen_id',
			'Result.result',
			'Result.created',
			'Result.modified'
			),
		'recursive' => 0,
		'limit' => 50);

The Same Thing Using Containable

var $paginate = array(
		'fields' => array(
			'id',
			'patient_id',
			'specimen_id',
			'result',
			'created',
			'modified'
			),
		'limit' => 50,
		'contain' => array(
			'ResultType' => array(
				'fields' => array(
					'ResultType.type',
					'ResultType.id'
					)
				),
			'Specimen' => array(
				'fields' => array(
					'Specimen.id',
					'Specimen.draw_date'
					)
				)
			)
		);

The SQL

SQL Generated from the Original $paginate var

SELECT COUNT(*) AS `count`
FROM `results` AS `Result`
LEFT JOIN `result_types` AS `ResultType`
ON (`Result`.`result_type_id` = `ResultType`.`id`)
LEFT JOIN `specimens` AS `Specimen`
ON (`Result`.`specimen_id` = `Specimen`.`id`)
LEFT JOIN `patients` AS `Patient`
ON (`Result`.`patient_id` = `Patient`.`id`)
WHERE 1 = 1

/* 1879 milliseconds */

SELECT `ResultType`.`type`, `Specimen`.`draw_date`, `Result`.`id`, `Result`.`patient_id`, `Result`.`specimen_id`, `Result`.`result`, `Result`.`created`, `Result`.`modified`
FROM `results` AS `Result`
LEFT JOIN `result_types` AS `ResultType`
ON (`Result`.`result_type_id` = `ResultType`.`id`)
LEFT JOIN `specimens` AS `Specimen`
ON (`Result`.`specimen_id` = `Specimen`.`id`)
LEFT JOIN `patients` AS `Patient`
ON (`Result`.`patient_id` = `Patient`.`id`)
WHERE 1 = 1
ORDER BY `Result`.`created` desc
LIMIT 50

/* 2106 milliseconds */

SQL Generated Using Containable Behavior

SELECT COUNT(*) AS `count`
FROM `results` AS `Result`
LEFT JOIN `result_types` AS `ResultType`
ON (`Result`.`result_type_id` = `ResultType`.`id`)
LEFT JOIN `specimens` AS `Specimen`
ON (`Result`.`specimen_id` = `Specimen`.`id`)
WHERE 1 = 1

/* 10 milliseconds */

SELECT `Result`.`id`, `Result`.`patient_id`, `Result`.`specimen_id`, `Result`.`result`, `Result`.`created`, `Result`.`modified`, `ResultType`.`type`, `ResultType`.`id`, `Specimen`.`id`, `Specimen`.`draw_date`
FROM `results` AS `Result`
LEFT JOIN `result_types` AS `ResultType`
ON (`Result`.`result_type_id` = `ResultType`.`id`)
LEFT JOIN `specimens` AS `Specimen`
ON (`Result`.`specimen_id` = `Specimen`.`id`)
WHERE 1 = 1
ORDER BY `Result`.`created` desc
LIMIT 50

/* 19 milliseconds */

The Difference

The first set of queries took nearly 4 seconds. The second: 29 milliseconds. Containable just gives you so much more control over what’s selected in your query. Using recursive => 0 still joined the patients table both times because it was associated in the model–even though in this case we didn’t need it. Using the Containable Behavior in the second example removed the patients table from the queries altogether.

This is one of the key pitfalls of using a framework like CakePHP; You can get things running quickly, but you have to go back and optimize. Otherwise you can build a heavy load on the server.

Resources

• CakePHP
• The Containable Behavior
• The Recursive Model Attribute

Similar Posts:

• CakePHP: Containable Behavior is Your Friend
• An Unexpected Problem with CakePHP and Email Elements
• Use Functions from Other Controllers While Maintaining MVC Architecture in CakePHP
• Getting Blueprint CSS & JavaScript Libraries Into Your CakePHP Layout
• Roll Your Own CakePHP Components

Tags: behaviors, CakePHP, containable, optimization, PHP, SQL

This entry was posted on Thursday, February 18th, 2010 at 10:21 am and is filed under CakePHP, PHP, containable behavior, web development. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.