What is the best practice to select users from DB ordered by online?

Question

I have table 'user', which has about 1 million users. I have query which select online users like this

select *, CASE WHEN abstime ( last_login_time + 600 ) >= now ( ) 
          THEN 3 ELSE 1 END AS onsitegen 
from user where blocked=0 order by onsitegen desc limit 3;

But it's too slow and I understand why. It's because I use order by onsitegen.

But what another method to select online users you can advise?

Answer 1

First create an index:

CREATE INDEX abc ON users( blocked, last_login_time );

and then try this query:

SELECT *
FROM (
  SELECT U.*, 3 As onsitegen
  from users u
  WHERE u.blocked = 0
    AND u.last_login_time >= now() - 600 * interval '1' second
  LIMIT 3
) x
UNION ALL
SELECT *
FROM (
  SELECT U.*, 1 As onsitegen
  from users u
  WHERE u.blocked <> 0
    AND u.last_login_time < now() - 600 * interval '1' second
  LIMIT 3
) y
ORDER BY onsitegen DESC
LIMIT 3