setting feature differences aside, i wanted to know how optimized myBB is.. coding wise, and database wise.... will we be expecting problems with HUGE databaseS?
i mean just by looking at the database i see REDUNDANT DATA storing...
for example:
mybb_posts and mybb_threads tables
both tables has a column called
subject which is the TITLE of the thread
I'm not php guru at all, but shouldnt redundant data be avoided? The title/subject of the thread should be in the mybb_threads tables, and the mybb_posts should just refer to it in a relational manner when retrieving its title?
what we have here is redundant data storing..
again im not the expert around here, just a guy with some know how
EDIT:
oh.. i've noticed also that usernames are repeatedly stored in each posts, threads, etc etc.. basically im seeing a lot of repeat storing of data instead of just their id's.. this could easily contribute to bigger database consumption...
and what about changing data? such as topic title, username for a user? does this mean that the whole database is updated instead of just the users table??????
if i was a long time user with thousands of posts, and an admin edited my name to something else, ALL THOSE POSTS will need to be updated too!
now im extremely worried....
I agree, ID's should be used where ever thats what i was tought.
Quote:I agree, ID's should be used where ever thats what i was tought.
same here. im pretty self though, but every book i read preaches against this practice.. that's always what i put in mind when i design my databases for projects.. avoid redundancy at all cost.
im not so good with inspecting php code, but based on what i see in the database, the update functions of MyBB will be doing some sort of SEARCH AND REPLACE if ever you wana update any of these redundant data.
i was soo happy the whole day when i decided to jump onto the myBB wagon..it seeemed so perfect! but this...trully a huge factor that needs reconsideration.
Im really itching to hear what the MyBB guys have to say about this....
From what I can tell there is a lot of work to optimise the code. Although I read on one of the MyBB dev blogs that there might be changing the code soon to be more class like.
The things you mention are redundant data, but it's there to speed up queries. Instead of querying the user table to just find the user name, if it's stored in the posts table, you save MySQL from doing a join. Since the post will only belong to one person, you get the information at input time and it's stored there and need never be looked up again. Of course it will need changing if something happens to the user but that's a simple update query.
I noticed it too when I looked at the code, but thinking about it, it does save some MySQL time.
I thought threads and the first post of the thread could have different titles, but I just checked and I'm wrong. So that is redundant data.
One of my problems is with post/thread/forum counts being stored. Someone posts or deletes a message and you have to update it, rather than a count statement. I haven't got a large enough forum to try different versions of code but I'm sure a COUNT statement would be much better than lots of post/forum update commands... especially on hosts that mounitor how many queries you use.
wow i always thought post and thread authors were stored by their id :/
@decswxaqz
yes i agree that it does speed up queries.. but only VERY LITTLE.. i coded this way before, not for faster queries but so i didnt have to do messy joins (saving development time, eg, lazy).. but generally it gives VERY LITTLE speed improvements... and payoff is
1) unnecessary bytes in Database
2) Synchronization problems
3) update problems
...so i dont think it's a good trade off.. hence i never coded that way again..
as with php code.. it looks very optimized.. i think that's fine right there. just the database im worried about.
it does make me feel a bit better that there's a large myBB site out there (messenger plus) that seems to have no problems... but what we see from the outsidie as viewers/guests/users isnt everything... so i dunno..
Quote:mybb_posts and mybb_threads tables
both tables has a column called subject which is the TITLE of the thread
mybb_threads stores the thread subject, mybb_posts stores post subjects. They're two different things.
Quote:EDIT:
oh.. i've noticed also that usernames are repeatedly stored in each posts, threads, etc etc.. basically im seeing a lot of repeat storing of data instead of just their id's.. this could easily contribute to bigger database consumption...
In the threads and posts tables we store the username as well, for a few reasons:
- If a guest makes a post they are allowed to enter a username as long as it isn't already in use.
- When a user is deleted, all records are gone from the users table for them. We need to display something in the post bit!
Quote:and what about changing data? such as topic title, username for a user? does this mean that the whole database is updated instead of just the users table??????
Inside MyBB, yes.
You edit the first post of a thread, it updates the thread subject too. You rename a user, it updates any existing columns in threads/posts tables with the same user id.
Quote:if i was a long time user with thousands of posts, and an admin edited my name to something else, ALL THOSE POSTS will need to be updated too!
No, they won't.
The showthread query queries the users table for the username. It selects it as userusername. If userusername exists, it is shown for the post, if not, the username from the posts table is shown.
It was designed so that the above two points I mentioned both functioned correctly, as well as allowed user names to be changed/updated without updating 10,000 posts.
MY I FEEL A LOT BETTER NOW!!!! WOOHOO!!
hehehe
Quote:The showthread query queries the users table for the username. It selects it as userusername. If userusername exists, it is shown for the post, if not, the username from the posts table is shown.
this does makes sense now.
nice to see there's a STRONG PLAN behind it's structure..
thanks for calming my nerves down Chris Boulton!