Announcement

Announcement Module
Collapse
No announcement yet.

mysql sometimes runs queries backwards?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql sometimes runs queries backwards?

    Why would mysql choose to run these queries in a reverse order?
    The second query is a lot better with less rows looked at.

    mysql> explain SELECT callrecs.*, noterecs.* FROM sontec.callrecs,sontec.noterecs where callrecs.call_id = noterecs.call_ref && callrecs.user_id = 'foo' ORDER BY callrecs.start DESC limit 10;
    Filesort
    +- TEMPORARY
    table temporary(noterecs,callrecs)
    +- JOIN
    +- Filter with WHERE
    | +- Bookmark lookup
    | +- Table
    | | table callrecs
    | | possible_keys PRIMARY,user_id
    | +- Unique index lookup
    | key callrecs->PRIMARY
    | possible_keys PRIMARY,user_id
    | key_len 3
    | ref sontec.noterecs.call_ref
    | rows 1
    +- Table scan
    rows 16695879
    +- Table
    table noterecs
    possible_keys call_ref_2
    2 rows in set (0.00 sec)

    mysql> explain SELECT callrecs.*, noterecs.* FROM sontec.callrecs,sontec.noterecs where callrecs.call_id = noterecs.call_ref && callrecs.user_id = 'foo' ORDER BY callrecs.start DESC limit 10;
    JOIN
    +- Bookmark lookup
    | +- Table
    | | table noterecs
    | | possible_keys call_ref_2
    | +- Index lookup
    | key noterecs->call_ref_2
    | possible_keys call_ref_2
    | key_len 3
    | ref sontec.callrecs.call_id
    | rows 3
    +- Filesort
    +- Filter with WHERE
    +- Bookmark lookup
    +- Table
    | table callrecs
    | possible_keys PRIMARY,user_id
    +- Index lookup
    key callrecs->user_id
    possible_keys PRIMARY,user_id
    key_len 32
    ref const
    rows 88
    2 rows in set (0.00 sec)

  • #2
    If I understand you correctly, you're saying that sometimes it chooses different execution orders for the same query. (If the queries are not identical please say so, I'm not checking.) This is often because statistics estimates are varying between runs. If you know that only one of the orders is good, you can use STRAIGHT_JOIN.

    Comment

    Working...
    X