with and without hints

wp_bp_activity.sql

SELECT count(DISTINCT a.id) FROM wp_bp_activity a USE INDEX (type)  WHERE a.is_spam = 0 AND a.id IN (957935) AND a.type != 'activity_comment' ORDER BY a.date_recorded DESC
    -> ;
+----------------------+
| count(DISTINCT a.id) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (2.31 sec)

mysql> SELECT count(DISTINCT a.id) FROM wp_bp_activity a  WHERE a.is_spam = 0 AND a.id IN (957935) AND a.type != 'activity_comment' ORDER BY a.date_recorded DESC;
+----------------------+
| count(DISTINCT a.id) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

# To prove it's not a query cache.. 

SELECT count(DISTINCT a.id) FROM wp_bp_activity a  WHERE a.is_spam = 0 AND a.id IN (957925) AND a.type != 'activity_comment' ORDER BY a.date_recorded DESC;
+----------------------+
| count(DISTINCT a.id) |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT count(DISTINCT a.id) FROM wp_bp_activity a  USE INDEX (type) WHERE a.is_spam = 0 AND a.id IN (957925) AND a.type != 'activity_comment' ORDER BY a.date_recorded DESC;
+----------------------+
| count(DISTINCT a.id) |
+----------------------+
|                    0 |
+----------------------+
1 row in set (3.86 sec)



### Explain with the HINT 

EXPLAIN SELECT count(DISTINCT a.id) FROM wp_bp_activity a USE INDEX (type)  WHERE a.is_spam = 0 AND a.id IN (957935) AND a.type != 'activity_comment' ORDER BY a.date_recorded DESC
    -> ;
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | a     | range | type          | type | 227     | NULL | 620546 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)


### Explain without the Hint

EXPLAIN SELECT count(DISTINCT a.id) FROM wp_bp_activity a  WHERE a.is_spam = 0 AND a.id IN (957935) AND a.type != 'activity_comment' ORDER BY a.date_recorded DESC;
+----+-------------+-------+-------+----------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys        | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+----------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | a     | const | PRIMARY,type,is_spam | PRIMARY | 8       | const |    1 |       |
+----+-------------+-------+-------+----------------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)