Re: Optimizer chooses full table scan

[Bill Willits]

I found a much simplier query modifier to improve the performance.
Just add the "STRAIGHT_JOIN" hint between the table specifications.
Check out the difference.

mysql> SELECT COUNT(DISTINCT a.SubscribeID)
-> FROM Subscribers a,Subscriptions b
-> WHERE a.ClientID=509
-> AND a.IsDeleted = 0
-> AND a.SubscribeID=b.SubscriberID;

+-------------------------------+
| COUNT(DISTINCT a.SubscribeID) |
+-------------------------------+
| 71568 |
+-------------------------------+
1 row in set (22 min 6.51 sec)

mysql> SELECT COUNT(DISTINCT a.SubscribeID)
-> FROM Subscribers a STRAIGHT_JOIN Subscriptions b
-> WHERE a.ClientID=509
-> AND a.IsDeleted = 0
-> AND a.SubscribeID=b.SubscriberID ;
+-------------------------------+
| COUNT(DISTINCT a.SubscribeID) |
+-------------------------------+
| 71568 |
+-------------------------------+
1 row in set (4.73 sec)