whoisgregg - 11:07 pm on May 25, 2012 (gmt 0)

It would seem to me that an index on `item_category` would be helpful.

MySQL database with several million rows, from which I want to select the top 10 occurrences of a particular item

One reaches a certain point where doing queries like this in real-time just isn't efficient anymore. Run a cronjob as often as you expect the rankings to change and store the results in a separate summary table.