Hi people
I have created a cms, wich is to slow, I have seen that the node query is
slow as I can show you, it uses 7228 seconds, showed on the debug level 2 on
cake. I am puting the model and the query. My question is: what will be the
best way to optimize this query, avoiding all relations and attaching it on
the fly or, what do ytou suggest, considering that nodes are queried for get
the active page and generate the menu, it means each time when the
app_controller is loaded, the menu is generated using the node model with
has relations with other model, and later the query is done for get the page
to be displayed
THanks
SELECT `Node`.*, `NodeType`.*, `ParentNode`.*, `I18n__meta_title`.`content`,
`I18n__meta_keywords`.`content`, `I18n__meta_description`.`content`,
`I18n__name`.`content`, `I18n__title`.`content`,
`I18n__title_for_navigation`.`content`, `I18n__body`.`content`,
`I18n__mobile`.`content`, `Node`.`id` FROM `nodes` AS `Node` LEFT JOIN
`node_i18ns` AS `I18n__meta_title` ON (`Node`.`id` =
`I18n__meta_title`.`foreign_key` AND `I18n__meta_title`.`model` = 'Node' AND
`I18n__meta_title`.`field` = 'meta_title') LEFT JOIN `node_i18ns` AS
`I18n__meta_keywords` ON (`Node`.`id` = `I18n__meta_keywords`.`foreign_key`
AND `I18n__meta_keywords`.`model` = 'Node' AND `I18n__meta_keywords`.`field`
= 'meta_keywords') LEFT JOIN `node_i18ns` AS `I18n__meta_description` ON
(`Node`.`id` = `I18n__meta_description`.`foreign_key` AND
`I18n__meta_description`.`model` = 'Node' AND
`I18n__meta_description`.`field` = 'meta_description') LEFT JOIN
`node_i18ns` AS `I18n__name` ON (`Node`.`id` = `I18n__name`.`foreign_key`
AND `I18n__name`.`model` = 'Node' AND `I18n__name`.`field` = 'name') LEFT
JOIN `node_i18ns` AS `I18n__title` ON (`Node`.`id` =
`I18n__title`.`foreign_key` AND `I18n__title`.`model` = 'Node' AND
`I18n__title`.`field` = 'title') LEFT JOIN `node_i18ns` AS
`I18n__title_for_navigation` ON (`Node`.`id` =
`I18n__title_for_navigation`.`foreign_key` AND
`I18n__title_for_navigation`.`model` = 'Node' AND
`I18n__title_for_navigation`.`field` = 'title_for_navigation') LEFT JOIN
`node_i18ns` AS `I18n__body` ON (`Node`.`id` = `I18n__body`.`foreign_key`
AND `I18n__body`.`model` = 'Node' AND `I18n__body`.`field` = 'body') LEFT
JOIN `node_i18ns` AS `I18n__mobile` ON (`Node`.`id` =
`I18n__mobile`.`foreign_key` AND `I18n__mobile`.`model` = 'Node' AND
`I18n__mobile`.`field` = 'mobile') LEFT JOIN `node_types` AS `NodeType` ON
(`Node`.`node_type_id` = `NodeType`.`id`) LEFT JOIN `nodes` AS `ParentNode`
ON (`Node`.`parent_id` = `ParentNode`.`id`) WHERE `NodeType`.`name` = 'page'
AND `I18n__name`.`content` = 'nosotros' AND `I18n__meta_title`.`locale` =
'esp' AND `I18n__meta_keywords`.`locale` = 'esp' AND
`I18n__meta_description`.`locale` = 'esp' AND `I18n__name`.`locale` = 'esp'
AND `I18n__title`.`locale` = 'esp' AND `I18n__title_for_navigation`.`locale`
= 'esp' AND `I18n__body`.`locale` = 'esp' AND `I18n__mobile`.`locale` =
'esp' LIMIT 1
<?php
class Node extends AppModel {
var $name = 'Node';
var $translateModel = 'NodeI18n';
var $actsAs = array(
'Tree',
'Translate' => array(
'meta_title' => 'MetaTitle',
'meta_keywords' => 'MetaKeywords',
'meta_description' => 'MetaDescription',
'name' => 'Name',
'title' => 'Title',
'title_for_navigation' => 'TitleForNavigation',
'body' => 'Body',
'mobile' => 'Mobile'
)
);
//The Associations below have been created with all possible keys, those
that are not needed can be removed
var $belongsTo = array(
'NodeType' => array(
'className' => 'NodeType',
'foreignKey' => 'node_type_id',
'conditions' => '',
'fields' => '',
'order' => ''
),
/*'User' => array(
'className' => 'User',
'foreignKey' => 'user_id',
'conditions' => '',
'fields' => '',
'order' => ''
),
'Site' => array(
'className' => 'Site',
'foreignKey' => 'site_id',
'conditions' => '',
'fields' => '',
'order' => ''
),*/
'ParentNode' => array(
'className' => 'Node',
'foreignKey' => 'parent_id',
'conditions' => '',
'fields' => '',
'order' => ''
)
);
var $hasMany = array(
'Comment' => array(
'className' => 'Comment',
'foreignKey' => 'node_id',
'dependent' => false,
'conditions' => '',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'exclusive' => '',
'finderQuery' => '',
'counterQuery' => ''
),
'ChildNode' => array(
'className' => 'Node',
'foreignKey' => 'parent_id',
'dependent' => false,
'conditions' => '',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'exclusive' => '',
'finderQuery' => '',
'counterQuery' => ''
)
);
var $hasAndBelongsToMany = array(
'Category' => array(
'className' => 'Category',
'joinTable' => 'nodes_categories',
'foreignKey' => 'node_id',
'associationForeignKey' => 'category_id',
'unique' => true,
'conditions' => '',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'finderQuery' => '',
'deleteQuery' => '',
'insertQuery' => ''
),
'Tag' => array(
'className' => 'Tag',
'joinTable' => 'nodes_tags',
'foreignKey' => 'node_id',
'associationForeignKey' => 'tag_id',
'unique' => true,
'conditions' => '',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'finderQuery' => '',
'deleteQuery' => '',
'insertQuery' => ''
)
);
}
?>
--
Carlos Eduardo Sotelo Pinto
PHP Senior Web Developer
Cell (preferred): (Mov)+51, 959980794 :: (Claro)+51, 952707662
http://www.csotelo.org
Skype: csotelop
Yahoo: csotelop
MSN: [email protected]
GTalk: [email protected]
GPG FP:697E FAB8 8E83 1D60 BBFB 2264 9E3D 5761 F855 4F6B
GNULinux RU #379182 || GNULinux RM #277661
--
Our newest site for the community: CakePHP Video Tutorials
http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others
with their CakePHP related questions.
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php
