MySQL Optimization

[Bennett 🚀]

Okay, I have a question about how I should arrange my tables and/or databases.

So, I'm making a forum system. I currently am thinking about having a static amount of tables in each database, which will be filled to the brim with entries.

Is this good? Or should I do different tables for each forum?

Should I do what I'm doing now?

This may be of a little more help:

Database 1:
forums_settings
-- Table structure: id, fid (forum id), setting, value
forums_categories
-- Table Structure: id, fid (forum id), name, order
forums_boards
-- Table Structure: id, fid (forum id), name, description, moderators, hidden, order
forums_threads
-- Table Structure: none yet, but will include an id and fid
forums_posts //gonna be a huge table o.o
-- Table Structure: none yet, but will include an id, fid, and thread id
forums_users
-- Table Structure: id, fid (forum id), username, password, email, ip

And then you insert all those in for like 5-6 forums or something.

Or, do I use "forumname_settings" tables and such?

I'm in a conundrum.

[Charles Stover]

I'm not sure there's a wrong way to do it per se. Technically, you should include multiple forums per table, but that would be a problem if there were a huge forum. This will also heavily increase the size of the table by forcing you to use things like int instead of tinyint.

The upside is that you can better draw statistics (COUNT(id) for the total posts on every forum, for example) and better update the entire software (UPDATE TABLE just once on one table instead of on countless tables).

I guess the line to draw is that if it is powered by the same PHP file, then it should use the same table. Whenever you run the same code on a separate server, then you would need a separate PHP file, thus you should use a separate database and thus a separate table.

But this is a really complex question. The fact that software like vB uses separate tables for multiple installations leads me to believe that there is an upside to doing so, which I imagine is the decrease in data stored in each table. So that would probably be the correct way of doing it.

Just brainstorming. I don't know the official answer. Both make sense to me, although I guess the more I think of it, the latter idea of multiple tables makes more sense and is supported by the way current forum services do it.

On a related note:

forums_boards
-- Table Structure: id, fid (forum id), name, description, moderators, hidden, order

No!
forums_moderators
-- Table Structure: board_id, user_id

SELECT user_id FROM moderators WHERE board_id = $board
SELECT username FROM users WHERE id = $user_id

Multiple data shouldn't be stored in a single cell, especially when that data relates to other tables.

Numbers are smaller than strings in a table, so your storing "123,213" in the moderators cell as a list of user #123 and #213 takes up 7 bytes. As a separate table, it would take 1 byte for board ID and 1 byte for user ID (assuming tinyint on both, which it likely wouldn't be; but with larger ints, you have longer strings, so it is still smaller to have a separate table), which would total 4 bytes - the size of your moderators lists is effectively halved.

[Bennett 🚀]

Okay, I think I'll stick with the multiple forums per table because it is only one file accessing the database.

And okay on the forums_moderators. Wouldn't have caught something like that.

I knew you'd post something to help me charles xD

[Martyn Dale]

No wrong way? Oh I can assure you there are certainly very wrong ways to do things.\

Given the nature of what your working on I cant say more. When I saw the title I thought I was going to be able to jump in here and do some magic, then I say the context.

[Bennett 🚀]

Yup.. sorry Martyn

[Charles Stover]

Jun 16, 2011 10:50:55 GMT -8 Martyn Dale said:

No wrong way? Oh I can assure you there are certainly very wrong ways to do things.\

Yes I know, but I meant out of the two ways presented.

Given the nature of what your working on I cant say more. When I saw the title I thought I was going to be able to jump in here and do some magic, then I say the context.

What is this about?

[Charles Stover]

Jun 16, 2011 10:40:55 GMT -8 Bennett 🚀 said:

Okay, I think I'll stick with the multiple forums per table because it is only one file accessing the database.

That may be best while you are still beta testing, since your table structure surely isn't finished yet, and it will be easier to alter just one table instead of many (unless ALTER TABLE *_users works, which I wouldn't be surprised at all if it did; I've never needed to use it, so forgive me for not memorizing that).

But when you do go live and get popular, I think each forum having its own table may be best, since it will decrease each table's size by removing the extra int and decreasing the need for things like bigint for keys that would otherwise be smaller without so many table rows.

[Bennett 🚀]

Jun 16, 2011 19:51:37 GMT -8 Charles Stover said:

Jun 16, 2011 10:40:55 GMT -8 Bennett 🚀 said:

Okay, I think I'll stick with the multiple forums per table because it is only one file accessing the database.

That may be best while you are still beta testing, since your table structure surely isn't finished yet, and it will be easier to alter just one table instead of many (unless ALTER TABLE *_users works, which I wouldn't be surprised at all if it did; I've never needed to use it, so forgive me for not memorizing that).

But when you do go live and get popular, I think each forum having its own table may be best, since it will decrease each table's size by removing the extra int and decreasing the need for things like bigint for keys that would otherwise be smaller without so many table rows.

Okay, that will take a lot of recoding at that point, but I suppose so.

[Charles Stover]

I dunno, probably not too much coding. Just change WHERE fid = $x to $x_table

[Bennett 🚀]

I suppose. Seems more than that, but it isn't lol.