# mysql sometimes runs queries backwards?

**URL:** <https://forums.percona.com/t/mysql-sometimes-runs-queries-backwards/1425>\
**Category:** Other MySQL® Questions\
**Created:** [June 3, 2010, 12:35pm UTC](https://forums.percona.com/t/mysql-sometimes-runs-queries-backwards/1425 "2010-06-03T12:35:20Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![williamt](https://avatars.discourse-cdn.com/v4/letter/w/977dab/32.png) [@williamt](https://forums.percona.com/u/williamt)\
**Post date:** [June 3, 2010, 12:35pm UTC](https://forums.percona.com/t/mysql-sometimes-runs-queries-backwards/1425/1 "2010-06-03T12:35:20Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [June 7, 2010, 6:04am UTC](https://forums.percona.com/t/mysql-sometimes-runs-queries-backwards/1425/2 "2010-06-07T06:04:17Z")

</div>

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.