Re: Slow SQL query

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 return 30.000 rows, according to the above (since you're OR'ing user_id and account_number). 

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?

That said, a query time of 49 seconds is absurd for just 300.000 rows even for a full table scan, and you should look into why.

This is probably why :) 

Name        | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment
order_order | InnoDB |      10 | Compact    | 672252 |           1765 |  1186988032 |               0 |     85639168 | 822083584 |         716087

Thanks for all the help,

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/7e5c0f5a-4edc-4dca-886e-99e12b5a01d9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.