Slow SQL query

Hi All,

I've been trying to figure out the proper way to index my data or construct my query so I can query orders in a reasonable amount of time. Is there a better way to index/query my data?

orders = Order.objects.exclude(status=Order.CART).filter(Q(user=user) | Q(account_number=account_number))
page = Paginator(orders, per_page=10).page(request.GET.get('page') or 1)

# Query_time: 49.414771  Lock_time: 0.000148 Rows_sent: 1  Rows_examined: 412843
SELECT COUNT(*) FROM `order_order` WHERE (NOT (`order_order`.`status` = 4) AND (`order_order`.`user_id` = 12345 OR `order_order`.`account_number` = '123456'));

class Order(models.Model):
    SUBMITTED, CART, SHIPPED, PARTIAL_SHIPPED, PROCESSED = 1, 4, 5, 6, 7
    STATUS_CHOICES = (
        (CART, 'Cart'),
        (SUBMITTED, 'Submitted on Website'),
        (PROCESSED, 'Processing'),
        (PARTIAL_SHIPPED, 'Partial Shipped'),
        (SHIPPED, 'Shipped'),
    )
    status = models.IntegerField('Order Status', choices=STATUS_CHOICES, default=CART, db_index=True)
    user = models.ForeignKey('account.UserProfile', null=True, blank=True)
    account_number = models.CharField(max_length=20, blank=True, db_index=True)

I also didn't use the indexes when I tried: WHERE `order_order`.`status` IN (1, 5, 6, 7)

If I take out the exclude(status=Order.CART) it properly uses the user and account indexes, but then it would have an improper count.

Thanks,

Collin

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/a82a24a1-825a-40da-a9ca-08a4e42f1167%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.