Re: Slow SQL query

> Den 29/10/2014 kl. 18.15 skrev Collin Anderson <cmawebsite@gmail.com>:
>
> 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 maybe a subquery would be better here to say: first get me all the orders for these people, _then_ filter out status=4.
>
> As far as user_id goes, 98% of the rows have user_id=None. Shouldn't the index be helpful there?

I'm not entirely sure how clever MySQL is with statistics on the indexes, so I can't be sure. But if user_id is almost always None, then this raises the question if your data model is appropriate. Your previous query examined 300.000 rows but returned only 1 row, which tells me either your query or data model is holding back information on the nature of your data.

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.

Erik

--
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/BADB2ABD-CD46-4965-8E2F-F6715FA27CCC%40cederstrand.dk.
For more options, visit https://groups.google.com/d/optout.