Even a query that is checking against an good index will result in a table scan if it is poorly written.  It seems some people think that HAVING can be a substitute for WHERE.  For example:

SELECT word_id
FROM forum.wordmatch
GROUP BY word_id
HAVING COUNT( word_id ) =53805;

This seems pretty straightforward; the query is trying to display how many entries have a word_id (indexed) greater than 53805.  But an explain shows that this is a table scan:

| table | type | possible_keys | key | key_len | ref | rows | Extra |
| wordmatch | index | NULL | word_id | 3 | NULL | 6374529 | Using index |

The problem is there is no WHERE clause, and HAVING just controls output.  So rewriting the query like so improves the performance by a factor of 10:

SELECT count( word_id )
FROM forum.wordmatch
WHERE word_id =53805;

| table | type | possible_keys | key | key_len | ref | rows | Extra |
| wordmatch | range | word_id | word_id | 3 | NULL | 641340 | Using where; Using index |

To quote mysql.com:

“Do not use HAVING for items that should be in the WHERE clause”. For example, do not write the following:

SELECT col_name FROM tbl_name HAVING col_name = 0;

Write this instead:

SELECT col_name FROM tbl_name WHERE col_name = 0;

The HAVING clause can refer to aggregate functions, which the WHERE clause cannot:

SELECT user, MAX(salary) FROM users
GROUP BY user HAVING MAX(salary) ;

SELECT DISTINCT ac2.category, ac0.name
FROM snaggy.a2_category ac1, snaggy.a2_category ac2, snaggy.a2_catalog ac0
WHERE ac1.category = 'GAME'
AND ac2.vendor_code = ac1.vendor_code
AND ac2.item_id = ac1.item_id
AND ac0.category = ac2.category
ORDER BY ac0.name

| table | type | possible_keys | key | key_len | ref | rows | Extra |
| ac0 | ALL | NULL | NULL | NULL | NULL | 145 |
| ac1 | ALL | category | NULL | NULL | NULL | 2886 | Using where; Using temporary; Using filesort
| ac2 | ALL | category | NULL | NULL | NULL |2886 | Using where |

CREATE TABLE `a2_category` (
`vendor_code` varchar(4) default NULL,
`item_id` int(5) default NULL,
`category` varchar(8) default NULL,
FULLTEXT KEY `category` (`category`)
) TYPE=MyISAM

Just adding an index helps makes the same query complete in a realistic timeframe:

ALTER TABLE a2_category ADD INDEX (item_id);

| table | type | possible_keys | key | key_len | ref | rows | Extra |
| ac0 | ALL | NULL | NULL | NULL | NULL | 145 | Using where |
| ac1 | ALL | item_id,category | NULL | NULL | NULL | 2886 | Using where; Using temporary; Using filesort |
| ac2 | ref | item_id,category | item_id | 5 | ac1.item_id | 3 | Using where |