Beginner's Fulltext Search Question

[hopelessliar]

I'm a real beginner with mysql/php but I've managed to put together a basic (1 table)mysql database and a web based search form which returns the relevant information. I've used a fulltext index (which includes most of the fields in the table) and this is what is now causing me some problems...

When I put this together, I learnt that searching against a fulltext index made use of a 'natural language' search function built into mysql and this seemed like a good idea to me. What I didn't realise was that every time the data in the table was added to or amended, the index needed to be recreated in order that the new information would be included in search results - told you I was new at this!

So, my questions are:
a) was I right to set up my search against a fulltext index like this or would it have been just as good to search against each field? (The database is small, it's unlikely ever to have more than around 200 rows, with around 14 fields per row)
b) if I stick to this arrangement, can I create some kind of sql statement and run it automatically perhaps once a day so that the index remains up to date without me having to manually update it? I assume this would be a cron job of some kind, which I've never done but if people say this is the way to go, then I guess I'll have to find out about cron jobs!

Any nudges in the right direction would be appreciated.

Thanks

 

[tsuji]

>What I didn't realise was that every time the data in the table was added to or amended, the index needed to be recreated in order that the new information would be included in search results
That's true but it is done automatically in the sense that you don't need to do it "manually":
without me having to manually update it
(I am not saying or implying anything about performance, just want to state the plain fact by design.)

 

[guelphdad]

your table is of insignificant size so you shouldn't worry about it in my opinion.

 

[r937]

your table is of insignificant size so you might get incorrect results from fulltext searching

go with LIKE instead



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[hopelessliar]

OK, first off, thanks for taking the time to reply. I've obviously midunderstood something here... Either I've described the problem badly or something's going wrong for me. It seems that whenever I add a new record to the database, it won't appear in any search results unless I manually recreate the index. (Obviously, in the light of what you're saying, I'll have to double check that is indeed happening now but I'm fairly sure it is. It seems you guys are telling me that shouldn't be the case.

r937 >> Why would I get incorrect results because my table is small? (All my test queries seem to return exactly what I'd expect.) If that's not a short simple thing to answer, perhaps you could point me in the direction of a page that explains it?

 

[r937]

the page that explains it is in da manual

Such a technique works best with large collections (in fact, it was carefully tuned this way). For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results. For example, although the word ``MySQL'' is present in every row of the articles table, a search for the word produces no results:

mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)

The search result is empty because the word ``MySQL'' is present in at least 50% of the rows. As such, it is effectively treated as a stopword. For large datasets, this is the most desirable behavior--a natural language query should not return every second row from a 1GB table. For small datasets, it may be less desirable.

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[hopelessliar]

OK, I see what you mean. Luckily for me, with the disparate data in this table, I don't think that's going to affect me and in fact, that's probably exactly the kind of behaviour I was looking for - I hope!

And I'll slap myself for not looking in the obvious place for the info.