Skip to main content
gamedev.net
🔒 Locked

[web] SQL and FULLTEXT

Started by choffstein Aug 28, 2006 at 8:59 PM 2 replies 700+ views
Original Post
choffstein
choffstein
Okay, I am having some serious issues with fulltext and getting it to work in all of my sql queries. I am currently using MySQL. So basically I have a structure that contains a title, description, school, and course. They are all of type TEXT, and at the bottom of the table I have: FULLTEXT(title,description,school,course). Infact, here is the whole table:

create table notes (
   id   int   not null, auto_increment, 
   title text,
   description text,
   school text,
   course text,
   primary key (id),
   FULLTEXT(title,description,school,course)
);

That should set up fulltext searching right? Okay, to test it, I inserted some data into my tables and put "Cornell University" as the school, with a title and description (no course number or description). I then did a query of:

select * from notes where match(title,description,school,course) against ('Cornell University');
It came up with 0 matches. What is the deal? Am I misunderstanding fulltext searching? Do I have to index my data every night or something? For the meantime I have gone back to "LIKE '%string%'" -- but it really isn't what I want. I want a good old fulltext search like it is advertised. Anyone know my issue? Essentially, I want to search for keywords across all of those fields. So that if a student searches for 'Cornell University', even if title, description, and course had nothing -- if school was like it, it would return the value. Thanks.
Sander
Sander
I am not sure how FULLTEXT indexing works over multiple columns. What happens if you query:

select * from notes where match(title,description,school,course) against ('', '', 'Cornell University', '');
markr
markr
Quote:
Original post by visage
That should set up fulltext searching right?


Yes, sounds ok.

Quote:

It came up with 0 matches. What is the deal?


The standard fulltext indexing algorithm ignores words which appear in > 50% of your rows. This is fully documented. Using the "IN BOOLEAN MODE" clause changes this behaviour. The reason for it is, that words which appear in > 50% of your corpus are considered uninteresting by the developers- this is based on experience with real data.

Quote:

Am I misunderstanding fulltext searching?


You should read the relevant sections of the MySQL docs. They explain these.

Quote:

Do I have to index my data every night or something?


No, it's maintained completely automatically. The only case where you have to manually reindex anything is if you change the min/max word length or stop words list in the config file. In this case you should issue a REPAIR TABLE.

Mark
choffstein
choffstein
changing to 'in boolean mode' worked perfectly. thanks mate!

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.

Sign in to reply to this topic.