matrix_jan
msg:4453925 | 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.
|
Andy Langton
msg:4453953 | 11:40 pm on May 15, 2012 (gmt 0) |
Thanks for the reply :)
To follow your steps:
With limit:
16.4201 sec
Without limit:
16.5387 sec
I don't really understand how I should be interpreting EXPLAIN, but it gives me the results below:
id 1
select_type SIMPLE
table data
type ALL
possible_keys NULL
key NULL
key_len NULL
ref NULL
rows 5794584
Extra Using where; Using temporary; Using filesort
|
|
I guess the problem is I need to index some of the affected columns?
|
matrix_jan
msg:4453989 | 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.
Good luck
|
Andy Langton
msg:4454059 | 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!?
20.9625 sec
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.
|
whoisgregg
msg:4457989 | 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.
|
Marino
msg:4458357 | 11:56 am on May 27, 2012 (gmt 0) |
Hello,
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)?
Best regards,
Marino
|
rlange
msg:4463947 | 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` [...] |
|
--
Ryan
|
|
