Re: Slow SQL query

2014-10-31 Thread Collin Anderson
Hi Erik, > Maybe Users doesn't belong in the Orders table? You could move the user to > a different table which stores the Order.id <-> user_id relation, which > would give a fast lookup on user_id and thus easy access to the Order.id > index. Interesting idea! I'll think about it. Thanks,

Re: Slow SQL query

2014-10-29 Thread Erik Cederstrand
> Den 29/10/2014 kl. 18.15 skrev Collin Anderson : > > Right, in practice it should be less than 1000 returned rows. > > I've changed the code to run the query without the status != 4 clause, and > manually filtering that out using python, because, yes, 97% of the rows are > status != 4. Or may

Re: Slow SQL query

2014-10-29 Thread Collin Anderson
Hi Erik, > Ok, you're not giving much of a chance to the query planner either. The > cardinality of status is 10, so status!=4 potentially means "give me 90% of > the rows". The cardinality of user_id is a mere 12, which potentially means > "give me 8% of the rows". Your query could easily re

Re: Slow SQL query

2014-10-27 Thread Erik Cederstrand
> Den 27/10/2014 kl. 14.55 skrev Collin Anderson : > > Hi Erik, > > No luck... > > mysql> show indexes in order_order; > +-++--+--++---+-+--++--++-+---+

Re: Slow SQL query

2014-10-27 Thread Collin Anderson
Hi Erik, No luck... mysql> show indexes in order_order; +-++--+--++---+-+--++--++-+---+ | Table | Non_unique | Key_name | Seq_in_index

Re: Slow SQL query

2014-10-27 Thread Erik Cederstrand
> Den 27/10/2014 kl. 13.20 skrev Collin Anderson : > > mysql> explain SELECT COUNT(*) > -> FROM `order_order` > -> WHERE `order_order`.`status` != 4 > -> AND (`order_order`.`user_id` = 12345 OR `order_order`.`account_number` > -> = 123456); > ++-+-+-

Re: Slow SQL query

2014-10-27 Thread Collin Anderson
mysql> explain SELECT COUNT(*) -> FROM `order_order` -> WHERE `order_order`.`status` != 4 -> AND (`order_order`.`user_id` = 12345 OR `order_order`. `account_number` -> = 123456); ++-+-+--+---

Re: Slow SQL query

2014-10-24 Thread Larry Martell
On Fri, Oct 24, 2014 at 12:30 PM, Collin Anderson 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(sta

Slow SQL query

2014-10-24 Thread Collin Anderson
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_numb