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

looks like many queries have to be maximised that way.. this is the third hack about indexing this week... vB guys may have to expand their actual roadmap to add indexation to the 3.0.2

[buro9]

Yeah, I've added quite a few after noticing it a few weeks ago, but none have been as dramatic as this one which is why I thought I should share.

To be frank... it's not really a hack. I just hoped that it might be allowed to stay just to help those who are affected by it

one vB owner just released a similar hack, so i suppose they will

[Xenon]

hmm, on my db scheme userid has already an index on that table.

are you sure you did not delete that one accidentally before?

[Crinos]

Originally Posted by Xenon

hmm, on my db scheme userid has already an index on that table.

Ditto ...

Anyway, 0.3 seconds constantly when accessing memberlist (almost 10,000 members) ... dunno if that's normal though... running 3.0.1...

[buro9]

Originally Posted by Xenon

hmm, on my db scheme userid has already an index on that table.

are you sure you did not delete that one accidentally before?

Hehe, I'm very sure that I didn't delete indexes

Might look back to see what version installed it.

I have 3 installations, 2 of which are upgrades and were installed over the last few days. They have the index. It was the fresh install version that I've been running on for 3 months that didn't have the index, and that's been hacked... I'm already suspecting that some naughty hack writer has done some nasty deed with my database.

Thankfully I keep all hacks that install well documented, so I'll go through tomorrow and piece together quite why I didn't have this essential index in place.

[buro9]

Originally Posted by buro9

Hehe, I'm very sure that I didn't delete indexes

Might look back to see what version installed it.

I have 3 installations, 2 of which are upgrades and were installed over the last few days. They have the index. It was the fresh install version that I've been running on for 3 months that didn't have the index, and that's been hacked... I'm already suspecting that some naughty hack writer has done some nasty deed with my database.

Thankfully I keep all hacks that install well documented, so I'll go through tomorrow and piece together quite why I didn't have this essential index in place.

Found it:

https://vborg.vbsupport.ru/showthread.php?t=63733

And in their code:
[sql]
ALTER TABLE `customprofilepic` DROP PRIMARY KEY;
ALTER TABLE `customprofilepic` ADD picture_id int(10) unsigned NOT NULL default '0' AUTO_INCREMENT PRIMARY KEY AFTER userid;
[/sql]

Their ought to be punishments for hackers that do things like this!

[Xenon]

so, as this isn't a hack, just a bugfix then, i have moved it t general mod discussions

As for VeloCD's hack, i assume it was just a mistake. The auto_increment field has to be the primary key, so he just forgot to readd the normal index back..

[buro9]

Originally Posted by Xenon

so, as this isn't a hack, just a bugfix then, i have moved it t general mod discussions

As for VeloCD's hack, i assume it was just a mistake. The auto_increment field has to be the primary key, so he just forgot to readd the normal index back..

Well, I was only thinking that the punishments should be to have their hack made open so others could directly fix things