Howto Design a Database to support efficient searching?

[erikcw]

Hi all,

I'm working on a database (MySQL) that will hold string data, with historical metrics for each string of data.

id, string, int1, int2, int3, timestamp

The data will be updated once a month with new metrics (int1, int2, int3).

I need to be able to do a SELECT on this table(s) and pull out all of the related query term rows from the string field. (a search for 'test this' will SELECT
test this,
test this thing,
this test,
test blah this thing.

At first, I was going to write the query using %LIKE%:
SELECT string, int1, int3 FROM table WHERE > timestamp > '2007-02-01 00:00:00' AND string='%LIKE%test this'

But that won't pull out the strings like 'test blah this thing' right? How do I do this kind of SELECT? Do I need a fulltext index?

Also, do I need to normailize this to make it efficient? I was thinking of pulling the string out and putting it in it's own table and then do a JOIN on the id.

If it makes a difference. It will be a rather large db. It should grow by about 2million rows per month.

Thanks so much for your help!
Erik

[txbakers]

First, you have to parsse out the search string on the space.

split the search string at the space, then get a count of the number of terms.

Then, you will need to bulid your query dynamically.

SO, if the string is "this or that"

you will get an array of 3 elements:
el[0] = "this";
el[1] = "or";
el[2] = "that";

The query will look something like this:

query = " select * from table where string like '%" + el[0] + "%' "
do while still elements:
query += " or string like '%" + el[i] + "%'"
loop
query += " order by string "

make sense?