Thread

Re: Speed up this query

Mark Stosberg <mark@summersault.com> — 2007-02-08T17:11:25Z

jeff.ward@gmail.com wrote:
> Hi all,
> 
> I'm fairly new to SQL, so this is probably a dumb way to form this
> query, but  I don't know another.
> 
> I want to see the usernames of all the users who have logged on
> today.  "users" is my table of users with id's and username's.
> "session_stats" is my table of user sessions where I store site
> activity, and it has a user_id column.
> 
> SELECT username FROM users WHERE id IN (SELECT DISTINCT user_id FROM
> session_stats $dateClause AND user_id!=0)

Jeff,

It looks like you need a JOIN instead:

SELECT username from users
  JOIN session_stats ON (users.id = session_stats.user_id)
  WHERE $dateClause AND user_id != 0);

Check that you also have indexes on both of those columns (check the
docs for "CREATE INDEX" for details. )

   Mark