Re^2: Help, RDBMS have taken over my brain!

I always wanted to code a forum that stores the bodies in a table like

PartID, MessageID, ThreadID, BodyPart VARCHAR(254)

so one could select a message with:

SELECT BodyPart FROM Body WHERE MessageId=? ORDER BY PartID

and all messages of a thread with:

SELECT MessageID, BodyPart FROM Body WHERE ThreadId=? ORDER BY MessageID, PartID

and get a preview of a message with:

SELECT BodyPart FROM Body WHERE MessageId=? where PartID=0

Fulltext search with preview:

SELECT MessageID, ThreadID, BodyPart FROM Body WHERE BodyPart LIKE '%foo%' ORDER BY MessageID

or, or, or... :-)

_Search, Ask, ^Know

Comment on Re^2: Help, RDBMS have taken over my brain!

Replies are listed 'Best First'.
Re^3: Help, RDBMS have taken over my brain! by mpeppler (Vicar) on Nov 24, 2004 at 14:46 UTC
That's all good (well, modulo a minor normalization quibble), except the full text search: `select ... from ... where BodyPart like '%foo%' ...` [download] On a small dataset this will be OK, but it won't scale, as it requires a table scan (or at the very least a full index scan if BodyPart is indexed) for databases that use B-Tree (and derived) indexes due to the leading wild-card (%). Michael	[reply] [d/l]
Re^4: Help, RDBMS have taken over my brain! by hubb0r (Pilgrim) on Nov 25, 2004 at 07:05 UTC
better (mysql at least) putting a fulltext index on BodyPart and use: `MATCH(BodyPart) AGAINST('foo')` [download]	[reply] [d/l]