homepage Welcome to WebmasterWorld Guest from 54.204.68.109
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Website
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Selecting the top 10 results by count in MySQL
Is this an efficient way of going about it?
Andy Langton




msg:4453885
 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 :)

 

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved