Re: Slow SQL query

> Den 27/10/2014 kl. 14.55 skrev Collin Anderson <cmawebsite@gmail.com>:
>
> Hi Erik,
>
> No luck...
>
> mysql> show indexes in order_order;
> +-------------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
> +-------------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
> | order_order | 0 | PRIMARY | 1 | id | A | 311492 | NULL | NULL | | BTREE | | |
> | order_order | 1 | order_order_8df3c379 | 1 | order_number | A | 311492 | NULL | NULL | | BTREE | | |
> | order_order | 1 | order_order_fbfc09f1 | 1 | user_id | A | 8 | NULL | NULL | YES | BTREE | | |
> | order_order | 1 | order_order_979d4f1e | 1 | account_number | A | 311492 | NULL | NULL | | BTREE | | |
> | order_order | 1 | order_order_48fb58bb | 1 | status | A | 10 | NULL | NULL | | BTREE | | |
> | order_order | 1 | order_order_482713d4 | 1 | user_id | A | 12 | NULL | NULL | YES | BTREE | | |
> | order_order | 1 | order_order_482713d4 | 2 | account_number | A | 155746 | NULL | NULL | | BTREE | | |
> | order_order | 1 | order_order_fb3214ea | 1 | status | A | 215 | NULL | NULL | | BTREE | | |
> | order_order | 1 | order_order_fb3214ea | 2 | account_number | A | 215 | NULL | NULL | | BTREE | | |
> | order_order | 1 | order_order_55eb7e10 | 1 | status | A | 215 | NULL | NULL | | BTREE | | |
> | order_order | 1 | order_order_55eb7e10 | 2 | user_id | A | 215 | NULL | NULL | YES | BTREE | | |
> +-------------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

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).

All MySQL knows is that it might be returning ca. 8% of the rows. That's about the threshold where MySQL gives up and simply does a full table scan.

Should the query really return ~30.000 rows in practice? If not, maybe you need to re-design the way you query your orders. Your status!=4 is no help at all, and user_id=12345 is killing the query.

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.

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/ED42A47D-83B0-4C73-88C0-6F2D858F2214%40cederstrand.dk.
For more options, visit https://groups.google.com/d/optout.