Hi there,
Ok , I am going to talk about my case which I feel its become really one of the most things I am spending time for it .
UBB is fast for 100,000 USERS and below, but last few months I notied a slow down in my server performance (MYSQL), and after many investigations , I found the reason, which is USERS and USER_PROFILE and USER_DATA tables reached to +300,000 Members (I am running a server with high specifications 8 CPU's + 32 GB/RAM )
As optimizations and normalizations , first thing I decided to do is
I did small changes in the tables (USER_PROFILE) :
I removed all extra fields which was ( 5 fields) because ( 99% of users not unsing it)
I removed all (ICQ, MSN, YAHOO etc ..) because of( 96% of my users not using it) .
And the result was increasing of mysql performance with over than 30%
What is the benefit of empty fields if my members not using it at all ??
I added another tables named USER_EXTRA and I put only 3 field on it :
(USER_ID , USER_FIELD_NAME, USER_FIELD_DETAILS)
So every user can have unlimited of extera field in their profile if they want .
Also, I suggest to remove un-necessary fields in USERS table and keep the important fields for login/authentication purpose , which will contain only 4 fields :
USER_ID, USER_LOGIN_NAME, USER_MEMBERSHIP_LEVEL, USER_IS_BANNED)
This is the most usage process in the within all script files , and for large numbers of users (+300,000) it’s a big challenging for webmasters, so we need only small table to manage this task , this will speed up the response time about +20% if I am not wrong .
Also , I am not happy with the Smarty plug-in , as I feel its slowing down the performance of the script , as I am still running the old ubb.inc.php for 6.x , because of some hacks I did for my websites since the old version 6.x (without smarty) and that hacks 100% faster than reading any posts or viewing and forum in the new 7.4 version, which is using smarty , I can prove this with evidence.
If you have new installation of UBB with less than 10,000 USERS you will not notice this problem .
What I want to conclude is that there are many area of this script need to tunning up, and specially in DB area.
Now I am facing a problem of upgrading , because every time I have to change all scripts which dealing with the USER_PROFILE table if I want to upgrade UBB !!
So , hope that my message reached clear to the developers and specially Mr. Rick .
Thank you,
AlNadabi
