Full text searching techniques - help!?

[IT Department]

Hi guys,

Myself and a colleague are very new to MySQL. We've been developing DB's for years in a environment known as 4D... We decided to dive in the deep-end with MySQL and try put it and us to the test.

We've written a script to INSERT 3 years worth of company emails into our new MySQL DB.

Our plan is to attach a web front end to the DB and allow certain staff to query all the emails in case we need to restore lost emails or need to read emails due to legal reasons... We've used the Query Analyser to query for keywords on the email body and we're finding the time it takes to bring back the results is very very very slow... :)

If anyone can help us or give advice on where to look/read I will buy you a pint!

I've got a gut feeling we're gonna need some special technique to index the email body field rather than use the built in Full Text search options in MySQL... I'm not sure why but I've got things like Boyer Moyer floating around in my head...


The machine is running XP Pro, MySQL 5.0 and has 2GB RAM.

Table type is MyISAMS.
Field Type: Medium Text.
Current Field Index: Full Text.


Is what we're trying to do achievable? Does it make sense to do what we're trying to do? How does gmail work when it comes to searching ka-zillions of emails :) Is it simply a case of putting wa-zillions of Megs of RAM in the machine so the Medium Text Full Index is loaded into memory?... or are their special techniques to index things further and speed the queries up?


Cheers,
Nick