Members List: Today's Birthdays... large number?

[Jeremy]

I said it earlier in this thread: Amount of posts, and amount of likes/points (influence).

Yep. But when 1.2 is out, I'm switching from memberlist, to "top users" (1.2 style you see now).

Out of nowhere, more and more spammers are registering to my site, and more and more are viewing the profiles, meaning that spammers found bad links in my members' profiles. I've been proactively banning these spammers, and getting rid of the link spam so that it goes back to normal.

For 5,000 members, 8,000 posts, 3,000 threads, I shouldn't have 1 gig being used. But it is eating up that much.

It may be a simple query, you're forgetting that you're loading 1 more for each member that you load.

See it this way. Even cached:

Search for birthday:

20 users = 20 queries
Click "view more" you add 20 more, right? That's 40 thus far. If you keep going, 60, 80, 100, 120, etc.
Every time you're fetching, you're adding 20 each time.

Like I said, a small number only use it - when I said this, I meant your members (and/or real visitors). Not spammers. Not bots. This trade-off is bad, because you're just wasting queries.

By your logic, any time you run the current member list... or thread.... you are doing a single query per user to get the information... Which, as we all know, is not the case.

If a user hasn't been active much in recent times, if they still exist, you deprive your new "notable" members from display. A simple paginated view all would be fine. Lists of members are found quite often in the software, I see no difference here.

Here's how the current members list fetches 20 users per page:

        return $this->fetchAllKeyed($this->limitQueryResults(
            '
                SELECT user.*
                    ' . $joinOptions['selectFields'] . '
                FROM xf_user AS user
                ' . $joinOptions['joinTables'] . '
                WHERE ' . $whereClause . '
                ' . $orderClause . '
            ', $limitOptions['limit'], $limitOptions['offset']
        ), 'user_id');

That executes 1 query and fetches 20 rows. The only difference would be WHERE `birthday` == 'today' (that's not right, but it gets my point across)

 

[Carlos]

By your logic, any time you run the current member list... or thread.... you are doing a single query per user to get the information... Which, as we all know, is not the case.

Learning more and more from you and this @jmurrayhead!

If a user hasn't been active much in recent times, if they still exist, you deprive your new "notable" members from display. A simple paginated view all would be fine. Lists of members are found quite often in the software, I see no difference here.

Hm. 'kay.

 

[Jeremy]

Learning more and more from you and this @jmurrayhead!

And hopefully realizing that its not as server intensive as you expect? You still aren't exposing your entire members list and will still reduce the spam you are receiving.

 

[Carlos]

And hopefully realizing that its not as server intensive as you expect?

*nods* mmmhm.

 

[jmurrayhead]

        return $this->fetchAllKeyed($this->limitQueryResults(
            '
                SELECT user.*
                    ' . $joinOptions['selectFields'] . '
                FROM xf_user AS user
                ' . $joinOptions['joinTables'] . '
                WHERE ' . $whereClause . '
                ' . $orderClause . '
            ', $limitOptions['limit'], $limitOptions['offset']
        ), 'user_id');

Or in .NET land, where I play:

var users = _userRepository
    .GetAll()
    .Where(u => u.BirthDate.Date == DateTime.Now.Date)
    .OrderBy(u => u.BirthDate)
    .Take(20)
    .ToList();

 

[Jeremy]

Or in .NET land, where I play:

var users = _userRepository
    .GetAll()
    .Where(u => u.BirthDate.Date == DateTime.Now.Date)
    .OrderBy(u => u.BirthDate)
    .Take(20)
    .ToList();

Well, we ain't in .NET land are we!? That looks a lot like JavaScript... Which I'm starting to like...

 

[jmurrayhead]

Well, we ain't in .NET land are we!? That looks a lot like JavaScript... Which I'm starting to like...

lol true. It's C#, my language of choice