2015-11-02 19:14 GMT-03:00 droberts <david.robe...@riverbed.com>: > Hi, I have a table that contains call records. I'm looking to get only > records for users who made the most calls over a particular time duration > in > an efficient way. > > calls() > > time, duration, caller_number, dialed_number > > > > -- query to get top 10 callers > select caller_number, count(1) from calls group by caller_number order by > calls desc limit 10 > > --my current query to get those callers > > select * from call where caller_number in (above query) > > > It works but I was hoping for something a little more efficient if anyone > has an idea. > > I think that almost every time based tables, should be partitioned. Also, depending on your workload you can create lazy views over the last entries in calls table during a particular time frame.
Probably in this particular case, you will want to dig into more underneath design in order to get the best performance. Doing a lazy view with that query, you can use the top n of it and get less callers if you need to (or more if you want to expand the feature). Hope it helps, -- -- Emanuel Calvo Twitter: 3manuek LinkedIn: http://ar.linkedin.com/in/ecbcbcb/
