| 10:00 pm on May 15, 2012 (gmt 0)|
What's the query time without LIMIT?
Count() is slowing the query time down when there is no index, do you have one on the table?
Also try explain + query to see what's going on.
| 11:40 pm on May 15, 2012 (gmt 0)|
Thanks for the reply :)
To follow your steps:
I don't really understand how I should be interpreting EXPLAIN, but it gives me the results below:
Extra Using where; Using temporary; Using filesort
I guess the problem is I need to index some of the affected columns?
| 2:03 am on May 16, 2012 (gmt 0)|
Not a problem,
Ok so you don't have an index on my_item. In order to shorten the query time index columns used in WHERE and ORDER BY. If you are using phpMyAdmin, it's very easy to add an index, just few clicks...
It would be great if you created a backup of the table before making any changes, in case if something goes left.
| 8:53 am on May 16, 2012 (gmt 0)|
I tried adding an index to first my_item then item_category, but that actually seems to be slower!?
Perhaps I just have too big a database for this server to handle quickly - I just want to make sure I'm not doing something basic incorrectly. Still, could be a pretty hefty hardware upgrade to take it down from 20 seconds to something more palatable.
| 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.
| 11:56 am on May 27, 2012 (gmt 0)|
I'm facing the same problems. Have you set innodb_file_per_table in your my.cnf?
When you say "a MySQL database with several million rows", you mean a table, or a set of tables? May be time to shard your big table(s) too? Would you post the table(s) structure(s)?
| 3:12 pm on Jun 11, 2012 (gmt 0)|
I'm a bit late, but...
I'm not sure how well the
ORDER BY clause works when you use a function, or what kind of optimizations or caching are done, but something worth trying might be to give the result of
COUNT() a name and then
ORDER BY that name.
SELECT COUNT( `my_item` ) AS `item_count` [...] ORDER BY `item_count` [...]