I have a MyISAM table with 100 million records.

The table has a PRIMARY KEY and a non-unique INDEX on a BIGINT column. My question is, how can we run a query on this INDEXED column and figure out which PRIMARY KEYs have the most matches. As of right now, this is pretty much as far as I can get it:

SELECT
primary_key_column,
count(*)
FROM
table_name
WHERE
indexed_column = some_numeric_value
GROUP BY
primary_key_column
ORDER BY
count(*) DESC
LIMIT 10;


I know there are tiny ways to improve this, but I am looking to go from several seconds to getting data within as fast as 100-200 ms. Any ideas? Am interested in hearing any algos, tuning tips, or even database specifically designed for this even if it is not MySQL.