Memberlist if Profile Pic is shown

[buro9]

My memberlist.php was taking some 6 or 7 seconds to display, and I only have 1,200 users.

Not good enough.

Poking around I saw in member.php this:

PHP Code:

" . iif($show['profilepiccol'], "LEFT JOIN " . TABLE_PREFIX . "customprofilepic AS customprofilepic ON (user.userid = customprofilepic.userid) ") . " 


It's part of the SQL that retrieves the data for the page. Notice how it joins to customprofilepic.userid?

Well that column isn't indexed on customprofilepic, so for each member in the main query (1,200 in my case) it did a full table scan looking for the userid within customprofilepic.

I added the index:
[SQL]
ALTER TABLE `customprofilepic` ADD INDEX ( `userid` )
[/SQL]

And now the page takes 0.07 seconds to display.

That's a 100x speed increase.

I suggest that anyone who has profile pics visible on their member list adds the index to MySql as above. It will make a marked difference in the page load time.

Cheers

David K