I want to create a filter of reputes for some period. A query is:

User.objects.exclude(date_joined__gt=end_period).filter( Q(reputes__date__gte=start_period) & Q(reputes__date__lte=end_period)).annotate(period_rep=Sum('reputes__value')).order_by('-is_active', '-period_rep').distinct()

After that i ran syncdb and migrate

python manage.py syncdb --all
python manage.py migrate forum

But this solution is very slow. Нow can I optimize the query?

Maybe there are other ways (without using reputes table)? General task is to get users who have been reputed from one date to another.

I've changed an ActionRepute class.

class ActionRepute(models.Model):
    action = models.ForeignKey(Action, related_name='reputes')
    date = models.DateTimeField(datetime.datetime.now, db_index=True)
    user = models.ForeignKey('User', db_index=True, related_name='reputes')
    value = models.IntegerField(db_index=True, default=0)
    by_canceled = models.BooleanField(default=False)

But the query has not working faster. Where the mistake?

I've tried this one. I've run

python manage.py sqlindexes forum

For getting indexes. After I've run

python manage.py dbshell

I've put

CREATE INDEX "forum_actionrepute_user_id" ON "forum_actionrepute" ("user_id");

into dbshell, but got an error

ERROR: relation "forum_actionrepute_user_id" already exists

I also tried

BEGIN; CREATE INDEX "forum_actionrepute_date" ON "forum_actionrepute" ("date"); COMMIT;
BEGIN; CREATE INDEX "forum_actionrepute_value" ON "forum_actionrepute" ("value"); COMMIT;

How I understood the trouble in annotate(period_rep=Sum('reputes__value')).

I've tried to create an other query

 User.objects.select_related('reputes__value', 'reputes').filter(reputes__in=ActionRepute.objects.filter(Q(date__gte=start_period) & Q(date__lte=end_period))).annotate(period_rep=Sum('reputes__value')).order_by('-is_active', '-period_rep').distinct()

But I had the same result. How can I optimizing the query (a calculation of sum of the reputes values)?

OK. I've gotten a generated query. In the query the Django platform has pasted GROUP BY by all columns in the query (there are about 15 columns). I think the problem in GROUP BY statement. What must I do for fix this trouble?

asked 07 Nov '11, 10:52

expert's gravatar image

expert
176101518
accept rate: 0%

closed 16 Nov '11, 11:56

Do anyone have any ideas?

(10 Nov '11, 04:24) expert

The question has been closed for the following reason "The question is answered" by expert 16 Nov '11, 11:56


Just add some indexes on your database tables and things should work much faster.

link

answered 07 Nov '11, 13:17

Hernani%20Cerqueira's gravatar image

Hernani Cerq... ♦♦
17.7k96884
accept rate: 53%

And the answer is the same. Its ok that query, but you can't go very far without adding some indexes. Django won't do it for you/OSQA, you need to add them yourself. Start by indexing the date columns and things should go much smoother.

(07 Nov '11, 16:55) Hernani Cerq... ♦♦

You need to do them manually in the db. Just by adding them on the code, nothing changed. Here's docs for mysql:

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

postgres:

http://www.postgresql.org/docs/current/static/sql-createindex.html

(09 Nov '11, 10:07) Hernani Cerq... ♦♦

You are trying to duplicate foreign keys and whatnot. Its not that hard to create an index (mysql sample):

create index some_cool_name on forum_actionrepute(date);
(09 Nov '11, 10:42) Hernani Cerq... ♦♦

I've created it, but the speed is same - very slow. Maybe need something else?

(09 Nov '11, 11:20) expert

I tried your original query in this site and it ran instantly. Something is not correct in your setup if it is that slow.

(09 Nov '11, 15:58) Hernani Cerq... ♦♦

On what kind of setups I must look? Maybe it depends on the size of the database (count of users and the reputes)? On different sets I've got different time.

(09 Nov '11, 16:32) expert

The post was updated.

(10 Nov '11, 07:28) expert
showing 5 of 7 show 2 more comments

I don't know, is it normal or not but my solution is working

        ar = ActionRepute.objects.filter(date__range=(start, end_period)).values('user__id').annotate(period_rep=Sum('value')).order_by('-period_rep').distinct()[a:b]
        users = User.objects.select_related('reputes__value', 'reputes').filter(id__in=[a['user__id'] for a in ar]).filter(reputes__date__range=(start, end_period)).annotate(period_rep=Sum('reputes__value')).order_by('-period_rep').distinct()

It's fast and doesn't depend on size of database. If I have mistakes, please show them to me.

link

answered 10 Nov '11, 09:10

expert's gravatar image

expert
176101518
accept rate: 0%

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×51
×12

Asked: 07 Nov '11, 10:52

Seen: 820 times

Last updated: 16 Nov '11, 11:56

powered by OSQA