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