Andy_Langton - 8:12 pm on May 15, 2012 (gmt 0)
I'm working with a rapidly growing dataset, and being somewhat of a beginner in MySQL, I'm concerned about making early mistakes :)
I have a MySQL database with several million rows, from which I want to select the top 10 occurrences of a particular item, by how often that item occurs in the database. So, I'm using the syntax below:
SELECT count(my_item), my_item
FROM data WHERE item_category='example_category'
GROUP BY my_item
ORDER BY count(my_item) DESC LIMIT 10;
Now this isn't slow, but it can take a good few seconds to complete when my_item is present in many thousands of rows. I can live with this, but I just want to get some advice on whether I'm doing this in an efficient way :)