Re: Efficiently query for the most recent record for a given user

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Aug 7, 2013 at 3:12 PM, Robert DiFalco <robert.difalco@xxxxxxxxx> wrote:
> Let's say I have a table something like this:
>
>    create table call_activity (
>         id int8 not null,
>         called timestamp,
>         user_id int8 not null,
>         primary key (id)
>         foreign key (user_id) references my_users
>     )
>
>
> I want to get the last call_activity record for a single user.  Is there ANY
> way to efficiently retrieve the last record for a specified user_id, or do I
> need to de-normalize and update a table with a single row for each user each
> time a new call_activity record is inserted? I know I how to do the query
> without the summary table (subquery or GROUP BY with MAX) but that seems
> like it will never perform well for large data sets. Or am I full of beans
> and it should perform just fine for a huge data set as long as I have an
> index on "called"?


Create an index over (user_id, called desc), and do

select * from call_activity where user_id = blarg order by called desc limit 1


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux