On Mar 22, 10:15 pm, Javier Guerra Giraldez <jav...@guerrag.com>
wrote:
> On Tue, Mar 22, 2011 at 10:06 PM, Brian Neal <bgn...@gmail.com> wrote:
> > I see. There are in fact only 15 forums. But why does it take 40
> > seconds? I can get much better results if I do a select on each forum
> > individually and combine them together in Python code. So in this case
> > 15 selects is far better than 1. Maybe that's an expected result in
> > some cases, but it sure surprised me.
>
> can you post the full SQL query?
>
Sure. This is what I came up with to reduce the long times I was
seeing (but it still is slow). This is probably going to get ugly in
email, maybe I should have dpasted it:
items = Post.objects.filter(topic__forum=8).order_by('-
creation_date').select_related('topic', 'user', 'topic_forum')[:30]
produces (time 0.289 seconds):
SELECT `forums_post`.`id`, `forums_post`.`topic_id`,
`forums_post`.`user_id`, `forums_post`.`creation_date`,
`forums_post`.`update_date`, `forums_post`.`body`,
`forums_post`.`html`, `forums_post`.`user_ip`, `forums_topic`.`id`,
`forums_topic`.`forum_id`, `forums_topic`.`name`,
`forums_topic`.`creation_date`, `forums_topic`.`user_id`,
`forums_topic`.`view_count`, `forums_topic`.`sticky`,
`forums_topic`.`locked`, `forums_topic`.`post_count`,
`forums_topic`.`update_date`, `forums_topic`.`last_post_id`,
`auth_user`.`id`, `auth_user`.`username`, `auth_user`.`first_name`,
`auth_user`.`last_name`, `auth_user`.`email`, `auth_user`.`password`,
`auth_user`.`is_staff`, `auth_user`.`is_active`,
`auth_user`.`is_superuser`, `auth_user`.`last_login`,
`auth_user`.`date_joined` FROM `forums_post` INNER JOIN `forums_topic`
ON (`forums_post`.`topic_id` = `forums_topic`.`id`) INNER JOIN
`auth_user` ON (`forums_post`.`user_id` = `auth_user`.`id`) WHERE
`forums_topic`.`forum_id` = 8 ORDER BY `forums_post`.`creation_date`
DESC LIMIT 30
This:
items = Post.objects.filter(topic__forum__in=forums).order_by('-
creation_date').select_related('topic', 'user', 'topic_forum')[:30]
produces (in 7.357 seconds)
SELECT `forums_post`.`id`, `forums_post`.`topic_id`,
`forums_post`.`user_id`, `forums_post`.`creation_date`,
`forums_post`.`update_date`, `forums_post`.`body`,
`forums_post`.`html`, `forums_post`.`user_ip`, `forums_topic`.`id`,
`forums_topic`.`forum_id`, `forums_topic`.`name`,
`forums_topic`.`creation_date`, `forums_topic`.`user_id`,
`forums_topic`.`view_count`, `forums_topic`.`sticky`,
`forums_topic`.`locked`, `forums_topic`.`post_count`,
`forums_topic`.`update_date`, `forums_topic`.`last_post_id`,
`auth_user`.`id`, `auth_user`.`username`, `auth_user`.`first_name`,
`auth_user`.`last_name`, `auth_user`.`email`, `auth_user`.`password`,
`auth_user`.`is_staff`, `auth_user`.`is_active`,
`auth_user`.`is_superuser`, `auth_user`.`last_login`,
`auth_user`.`date_joined` FROM `forums_post` INNER JOIN `forums_topic`
ON (`forums_post`.`topic_id` = `forums_topic`.`id`) INNER JOIN
`auth_user` ON (`forums_post`.`user_id` = `auth_user`.`id`) WHERE
`forums_topic`.`forum_id` IN (3, 4, 14, 2, 6, 9, 8, 10, 5, 7, 11, 12,
13, 16) ORDER BY `forums_post`.`creation_date` DESC LIMIT 30
Each Post has a foreign key to a Topic and each Topic has a foreign
key to a Forum.
I guessing all those INNER JOIN's are killing me here.
Thanks.
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
