Enforcing a query in MySql to use a specific index

Question

Hi, I have large table. consisting of only 3 columns (id(INT),bookmarkID(INT),tagID(INT)).I have two BTREE indexes one for each bookmarkID and tagID columns.This table has about 21 Million records. I am trying to run this query:

SELECT bookmarkID,COUNT(bookmarkID) AS count
FROM bookmark_tag_map
GROUP BY tagID,bookmarkID
HAVING tagID IN (-----"tagIDList"-----) AND count >= N

which takes ages to return the results.I read somewhere that if make an index in which it has tagID,bookmarkID together, i will get a much faster result. I created the index after some time. Tried the query again, but it seems that this query is not using the new index that I have made.I ran EXPLAIN and saw that it is actually true. My question now is that how I can enforce a query to use a specific index? also comments on other ways to make the query faster are welcome. Thanks

Answer 1

Take a look at the Index Hint Syntax

Answer 2

First of all, having tagID in the group by clause, but not in the selected fields is strange, because you will not know which tag the count belongs to. Are you sure you want the tagID in group by? If you just want the most frequent bookmarks for certain sets of tags, then you don't need it. My proposal to make it faster is to move the tagID condition into the where clause:

SELECT bookmarkID,COUNT(bookmarkID) AS count
FROM bookmark_tag_map
WHERE tagID IN (-----"tagIDList"-----)
GROUP BY bookmarkID
HAVING count >= N

That should be much faster, but for 12 million records probably still not fast enough to use it on a frequently called page without caching.