I actually don't want to count anything, I believe this query is
executed by the paginator. I didn't write this query, it's auto
generated by cake (I see it at the bottom of the screen with debug
level 2). So, how would I replace left joins with inner joins in it?
This is the table structure:
DROP TABLE IF EXISTS `migration2010`.`articles`;
CREATE TABLE `migration2010`.`articles` (
`id` int(12) NOT NULL auto_increment,
`author_id` int(12) default NULL,
`title` varchar(256) default NULL,
`title_for_seo` varchar(256) default NULL,
`subhead` varchar(256) default NULL,
`body` text,
`website_id` int(12) default NULL,
`is_sidebar` tinyint(4) default '0',
`is_page` tinyint(4) default '0',
`is_active` tinyint(4) NOT NULL default '1',
`date_live` timestamp NULL default NULL,
`date_added` timestamp NULL default NULL,
`date_modified` timestamp NULL default NULL,
`updated_by_author_id` int(12) default NULL,
`is_web_only` tinyint(4) default '0',
`article_id` int(12) default NULL,
`is_feature` tinyint(1) unsigned NOT NULL,
`slug` varchar(256) default NULL,
`image_url` text,
`image_size` enum('large','medium','small','thumb') default NULL,
`image_caption` varchar(512) default NULL,
`cloprofile_thumb_url` text,
`cloprofile_thumb_caption` varchar(512) NOT NULL,
`is_flipper` tinyint(1) NOT NULL default '0',
`snippet` varchar(140) default NULL,
`author_name` varchar(200) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3857 DEFAULT CHARSET=latin1;
This is the output of "explain select ... ":
http://manydemons.com/archive/2010/trash/explain_1.html
Article has the following relations:
//The Associations below have been created with all possible keys,
those that are not needed can be removed
var $belongsTo = array(
'Author' => array(
'className' => 'Author',
'foreignKey' => 'author_id'
),
'Website' => array(
'className' => 'Website',
'foreignKey' => 'website_id'
),
'ParentArticle' => array(
'className' => 'Article',
'foreignKey' => 'article_id'
)
);
// var $hasOne = array(
// 'ChildArticle' => array(
// 'className' => 'Article',
// 'foreignKey' => 'article_id',
// 'dependent' => false
// )
// );
var $hasMany = array(
'ChildArticle' => array(
'className' => 'Article',
'foreignKey' => 'article_id',
'dependent' => false
)
);
var $hasAndBelongsToMany = array(
'Category' => array(
'className' => 'Category',
'joinTable' => 'articles_categories',
'foreignKey' => 'article_id',
'associationForeignKey' => 'category_id',
'unique' => true
),
'Material' => array(
'className' => 'Material',
'joinTable' => 'articles_materials',
'foreignKey' => 'article_id',
'associationForeignKey' => 'material_id',
'unique' => true
),
'Tag' => array(
'className' => 'Tag',
'joinTable' => 'articles_tags',
'foreignKey' => 'article_id',
'associationForeignKey' => 'tag_id',
'unique' => true
)
);
So I think maybe this was causing the problem: article hasOne and
hasMany childarticles. So I removed hasOne and I think it's working
fine now.
thanks all!
-Victor
On Oct 8, 1:14 pm, Sanza <[email protected]> wrote:
> Can you explain better what you wont to count, please?
> This query seem a bit strange, can you explain the kinds of relations?
> You can check/add the indexes on foreign keys and may be you can
> change: "LEFT JOIN authors" with "INNER JOIN authors" (if article
> hasOne author ) ... and:... "LEFT JOIN websites" with "INNER JOIN
> websites" if article hasOne website , but all depends of yours
> relations..
> Sanza
>
> On 8 Ott, 12:08, victor piousbox <[email protected]> wrote:
>
> > I have a website with a bunch of articles. This is one of the queries
> > executed by I assume the Paginator:
>
> > SELECT COUNT(*) AS `count` FROM `articles` AS `Article` LEFT JOIN
> > `authors` AS `Author` ON (`Article`.`author_id` = `Author`.`id`) LEFT
> > JOIN `websites` AS `Website` ON (`Article`.`website_id` =
> > `Website`.`id`) LEFT JOIN `articles` AS `ParentArticle` ON
> > (`Article`.`article_id` = `ParentArticle`.`id`) LEFT JOIN `articles`
> > AS `ChildArticle` ON (`ChildArticle`.`article_id` = `Article`.`id`)
> > WHERE 1 = 1
>
> > It takes 2ms when I just go to /articles/index, but if I edit and save
> > an article, the same query takes 123000ms (that's two minutes). I have
> > 3300 articles.
>
> > Why does this happen? And how can I avoid it?
>
> > _V
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
