Re: Slow SQL query

On Fri, Oct 24, 2014 at 12:30 PM, Collin Anderson <cmawebsite@gmail.com> wrote:
> 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.

You'll probably get more replies on the mysql list. But you could try
running EXPLAIN on the query. Also, what if you change it to:

SELECT COUNT(*)
FROM `order_order`
WHERE `order_order`.`status` != 4
AND (`order_order`.`user_id` = 12345 OR `order_order`.`account_number`
= 123456');

--
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/CACwCsY4tkC8BRfbW3QESKHdK2MVqKXn3B%3DrPq2xkPoyzkxcgxw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.