Table optimization question

ben_g

I have a message board on my site that uses mysql and am trying to optimize the database to use the least resources on my server.

My tables look like so;

Users (Username, Password, Pin, Email, Type)

Username = char(20) NOT NULL PRIMARY KEY
Password = char(20) NOT NULL
Pin = int(4) NOT NULL
Email = char(60) NOT NULL
Type = int(1) NOT NULL

Posts (PostID, ParentID, Children, DateTime, Username, Subject, Title, Body)

PostID int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY
ParentID int(10) NOT NULL DEFAULT 0
Children int(4) NOT NULL DEFAULT 0
DateTime TIMESTAMP NOT NULL
Username char(20) NOT NULL
Subject int(1) NOT NULL DEFAULT 0
Title char(100) NOT NULL
Body text

Am I better to store the message body (currently in Posts) in a separate table because it is not a fixed size and can be anything from 0 to 5000 character long?

Or should I make it a char column with a fixed size of 5000 characters?

My site is running on a shared server.

piersk

I think for speed, then you should keep it all in the same table (there's less chance of something going wrong when writing the script as well). In short, I dont think you would gain anything from having the message body in a different table.

ben_g

I thought it might speed up the drawing of the index page as this does not require the body information.

It is also the page that makes that gets the most hits.

dannys

Why not try both? It's not going to take long to knock out both sets of tables and a nice loop to insert a bucket load of test data.

I'm also interested in your use of char() - is it really that much faster than varchar() (which I currently use for space efficiency) ?

jmcneese

if you do not SELECT the body data, how will this slow down queries ran for your index page? 😕

piersk

Again, it wouldn't noticably speed the database access up if you have written a query that doesnt select the message body.

if you have written (and want to keep) a query that looks like:

select * from $table_name

then you might want to have the body in another table, but the only way you would notice the difference was if you have a very slow computer.

piersk

(just like to point out that that last post of mine was meant to be in answer to ben_g's second question)

ben_g

thanks for you help

I will keep it as is.

undertow

I personally like using the Text type for large things like message board posts. This way I don't have to think about what number I need to have for Char or Varchar to make it long enough for people. Text is very large if I'm not mistaken, but I think it only uses space for how much text there actually is in a record.

Also I think that historically it's less efficient because it's more flexible, but on today's systems without heavy load it probably won't be noticable.

Or I could be completely wrong :p.

piersk

No, you're right. You can't assign a limit to the size of a text field.

ultraslacker

I don't think you will notice a difference, but if I were expecting to get a huge volume on posts than I would take the text out - you should do some testing to see if there is a noticeable gain. I haven't noticed much difference on tables with less than a million rows, 1k updates/inserts per day. Be sure to check how the tables performs with update/insert as this is the big hit!

For char versus varchar - I use char on a zipcode table with 6.4 million rows, but nowhere else. I probably should have though I chose instead to leave mysql for postgres.

It's not normally useful to split a table into different tables just because the rows gets 'big'. To access a row, the biggest performance hit is the disk seek to find the first byte of the row. After finding the data most new disks can read the whole row fast enough for most applications. The only cases where it really matters to split up a table is if it's a dynamic row size table (see above) that you can change to a fixed row size, or if you very often need to scan the table and don't need most of the columns. See section 8 MySQL Table Types.

From mysql manual tips

mattr

TEXT (IMAGE, etc.) columns on MS SQL, Sybase (and I think Oracle) are stored off-row in a special heap area so that you do not run into problems in which you have to perform workarounds for large amounts of data. We have a 4.4 million row table which is around 7GB (which has a text column) in our message boards and performance is not noticeably slower than it was with 100,000 rows. Choose your RDBMS wisely.

I do not think CHAR vs. VARCHAR should prove to be a large performance difference in theory. Choose your datatype that fits your data, you're hardly likely to run into measurable peformance problems with CHAR( X ) vs. VARCHAR( X ) soley due to VARCHAR "inefficiencies". I would expect that as the size of X tends to infinity (and disk space and RAM capacity held constant) CHAR would become increasingly inefficient since indexes and rows need to fit on a finite amount of space, although again it depends on how intelligent your RDBMS is and whether or not it stores the extra whitespace in RAM or not.