开发者

Efficient db design for retrieving 'most popular' table rows

开发者 https://www.devze.com 2022-12-19 10:22 出处:网络
I am planning to create a mysql 5 (myISAM) table that will contain x thousand rows of data.Each row will have a count field, & the rows with the 20 highest count values will be retrieved 开发者_高

I am planning to create a mysql 5 (myISAM) table that will contain x thousand rows of data. Each row will have a count field, & the rows with the 20 highest count values will be retrieved 开发者_高级运维quite a lot, probably on a one-for-one ratio to every row update. The x thousand rows not in this 20 will not typically be retrieved.

What are the choices wrt choosing an efficient method of querying for the 20 top count values. Data does not have to be realtime live but it helps!


Add an index to the count field, then just a normal query:

SELECT * FROM myTable ORDER BY countField DESC LIMIT 20;


Create an index on the count field. Done.


Every five minutes, empty out and then copy the top 20 to a separate table. To get the top 20, just consult that table. Done!

(Or, you could spend the next few hours tweaking your query and parameters and indices and making it all "just right".)

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号