Do you have a link to the MySQL forums discussion?

We think that upgrading to a new version of MySQL 5.5+ will likely solve this issue so we'll leave the patch for people who want to keep running mysql 5.1 but we won't apply it to the kernel because it is likely to actually hurt performance for people who did upgrade (as per the advice of the mysql forums).

The strange thing is from the explain we should be looking at more rows with the force index, but it seems to work out faster....

Ok, so I have a site locally that has 13527 users in it. Looking at the count query, when it's not in the query cache:

optimizer: 0.069 sec
force index: 0.019 sec

This is reasonably reproduceable, by changing the userId you can avoid the query cache. We're on MySQL 5.1.66 from Debian squeeze.
I've attached the explains against our live DB as HTML.

SELECT count:yellow_star: FROM SAKAI_REALM;
SELECT count:yellow_star: FROM SAKAI_REALM_ROLE;
SELECT count:yellow_star: FROM SAKAI_REALM_RL_GR;
SELECT count:yellow_star: FROM SAKAI_REALM_RL_FN;
SELECT count:yellow_star: FROM SAKAI_REALM_FUNCTION;

SAKAI_REALM: 76253
SAKAI_REALM_ROLE: 39
SAKAI_REALM_RL_GR: 551716
SAKAI_REALM_RL_FN: 7509053
SAKAI_REALM_FUNCTION: 258

Here are the table sizes in our production db (rows):

SAKAI_REALM: 158680
SAKAI_REALM_ROLE: 40
SAKAI_REALM_RL_GR: 2582045
SAKAI_REALM_RL_FN: 35800127
SAKAI_REALM_FUNCTION: 297

Our production mysql is 5.1.68

This is from our slow query log from a period last year (running 2.8), output from mysqldumpslow -s=t -t 15

Count: 15341 Time=5.00s (76760s) Lock=0.02s (244s) Rows=1.0 (15341), sakaiuser[sakaiuser]@6hosts
select count(N) from SAKAI_REALM_RL_FN MAINTABLE LEFT JOIN SAKAI_REALM_RL_GR GRANTED_ROLES ON (MAINTABLE.REALM_KEY = GRANTED_ROLES.REALM_KEY AND MAINTABLE.ROLE_KEY = GRANTED_ROLES.ROLE_KEY), SAKAI_REALM REALMS, SAKAI_REALM_ROLE ROLES, SAKAI_REALM_FUNCTION FUNCTIONS where (ROLES.ROLE_NAME in('S','S') or (GRANTED_ROLES.USER_ID = 'S' AND GRANTED_ROLES.ACTIVE = N)) AND FUNCTIONS.FUNCTION_NAME = 'S' AND REALMS.REALM_ID in ('S') AND MAINTABLE.REALM_KEY = REALMS.REALM_KEY AND MAINTABLE.FUNCTION_KEY = FUNCTIONS.FUNCTION_KEY AND MAINTABLE.ROLE_KEY = ROLES.ROLE_KEY

with an actual example (note the very large Rows_examined value).

1. Time: 120810 6:00:04

2. User@Host: sakaiuser[sakaiuser] @ srvslscle007.uct.ac.za [137.158.155.54]

3. Query_time: 1.238584 Lock_time: 0.000079 Rows_sent: 1 Rows_examined: 108164
   SET timestamp=1344571204;
   select count(1) from SAKAI_REALM_RL_FN MAINTABLE LEFT JOIN SAKAI_REALM_RL_GR GRANTED_ROLES ON (MAINTABLE.REALM_KEY = GRANTED_ROLES.REALM_KEY AND MAINTABLE.ROLE_KEY = GRANTED_ROLES.ROLE_KEY), SAKAI_REALM REALMS, SAKAI_REALM_ROLE ROLES, SAKAI_REALM_FUNCTION FUNCTIONS where (ROLES.ROLE_NAME in('.anon','.auth') or (GRANTED_ROLES.USER_ID = '0a8199d4-217f-4536-9c73-00d5257b9310' AND GRANTED_ROLES.ACTIVE = 1)) AND FUNCTIONS.FUNCTION_NAME = 'annc.all.groups' AND REALMS.REALM_ID in ('/site/375b9f7b-edb2-4c2f-bc70-09bf0adec334') AND MAINTABLE.REALM_KEY = REALMS.REALM_KEY AND MAINTABLE.FUNCTION_KEY = FUNCTIONS.FUNCTION_KEY AND MAINTABLE.ROLE_KEY = ROLES.ROLE_KEY;

Since adding the 'force index', realm queries no longer show up in our slow query log. However, it's hard to demonstrate slow performance under controlled conditions (except perhaps a realistic and intensive load test). Running EXPLAIN for some of the queries (without the force index changes) now shows different results on our db than was observed under load, so the query optimizer may be choosing different strategies under different conditions, some of which are sub-optimal. We experienced severe load issues from these queries on 3 or 4 different occasions, somewhat unpredictably.

One of our mysql 5.5 dev boxes appears to show different results for the choice of keys in EXPLAIN (i.e. chooses correct keys without use of force index), though hard to say whether that's a mysql version issue or because the tables there are unfragmented (as they're typically restored from a mysql dump).

So I'd still conclude that unless the 'force index' change shows adverse effects on smaller databases, the change is a precautionary measure which may benefit some production sites on some versions of mysql under significant load.