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

[Beechbone]

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... :-)

[mpeppler]

That's all good (well, modulo a minor normalization quibble), except the full text search:

select ... from ... where BodyPart like '%foo%' ...

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

[hubb0r]

better (mysql at least) putting a fulltext index on BodyPart and use:


MATCH(BodyPart) AGAINST('foo')