One Table vs Two Tables

[neoform]

I'm making a messaging system that has the fields:

 

id INT(10)
posted_on DATETIME (INDEX)
user_id MEDIUMINT( (INDEX)
recipient_user_id MEDIUMINT( (INDEX)
body TEXT

 

I've been torn as to whether or not I should split the body field into a second table in order to speed things up. I ran some tests with SELECTs and found that using a single table is usually between 20% to 100% faster (depending on how many rows are being selected and if the body is included in the SELECT).

 

Where the single table is slower though, is on INSERT, UPDATE and DELETE, because of the reindexing of the DYNAMIC table (single table) vs the FIXED table (two table)..

 

So the question is, which is the better option, assuming I'm actually going to fill this table up with more than a million rows.

 

BTW, the tests I did were on tables with 300,000 rows packed with identical random data.

[fenway]

It's because TEXT fields behave very differently than {VAR}CHAR fields.  Do you really need more than 65K message?

[neoform]

In this instance, no.

 

What's the big diff between VARCHAR(65000) and TEXT?

[fenway]

Well, first, I don't think you need 65K either... but TEXT automatically requires the use of temporary tables (i.e. written to disk), so that makes everything disk-bound and quite slow.

[neoform]

So would it be recommended to stick to a single table with a large VARCHAR? or Split to two tables with a large VARCHAR..

[fenway]

If you pick a reasonably sized varchar, you can keep it in the same table...

[Barand]

What's the big diff between VARCHAR(65000) and TEXT?

 

The varchar will only hold 255 chars

[neoform]

I'm using MySQL 5.0.8

 

VARCHAR has been bumped up to allow up to 65k chars..

[fenway]

I'm using MySQL 5.0.8

 

VARCHAR has been bumped up to allow up to 65k chars..

One of the few reasons to actually upgrade (other than index merge).