mysql sometimes runs queries backwards?

MySQL & MariaDB Other MySQL® Questions
1

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.