Re: Approach to extract top records from table based upon aggregate

On Mon, Nov 2, 2015 at 4:14 PM, droberts <david(dot)roberts(at)riverbed(dot)com> wrote:
> 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.

How fast is it running, and how fast do you expect it to run? To make
that faster than that, you're going to have to rethink things a little
bit. For example, you could narrow the search down to a time range,
or maybe you could keep a running internalization of the count.

This query looks suspicious:
select caller_number, count(1) from calls group by caller_number order
by calls desc limit 10

you're ordering by the entire table, which is almost certainly a
mistake. It probably needs to look like:

select *
from
(
select
caller_number,
count(1) as count_calls
from calls
group by caller_number
) q order by count_calls desc limit 10;

merlin