> Den 27/10/2014 kl. 14.55 skrev Collin Anderson <[email protected]>: > > 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 [email protected]. To post to this group, send email to [email protected]. 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.
