FULLTEXT indexing problem

trying to update Mysql fulltext index

[chrisdgreen]

Hi all,

I have table called 'profile' i have altered the table

ALTER TABLE profile ADD FULLTEXT ( oname , etc...);

I have 14 cols in the alter list - (oname, etc..)

This creates an index that appears to be empty.
My dbase has 1800 rows the cardinality says none.
I'm using MySQL 3.23.56

Any ideas where I may be goin wrong?

If I can get this working will I have to reapply the process each time a change happens manually or does it update automatically?

Thanks in advance

Chris

[coopster]

Welcome to WebmasterWorld, chrisdgreen.

The columns in your list, they can be created only from CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; partial indexing is not supported and any prefix length is ignored if specified.

This creates an index that appears to be empty.

An index does not hold the values of the data in the table itself but rather specifies a means to retrieve the physical data using a logical path. To use a FULLTEXT index in MySQL you use the MATCH ... AGAINST [dev.mysql.com] function.

[chrisdgreen]

Thanks for getting back!

All my fields are definately only text fields

ALTER TABLE `profile` DROP INDEX `full_index`, ADD FULLTEXT KEY `oname` (`oname`(5),`otype1`(5),`otype2`(5),`olocation`(5),`aims`(5),`affil`(5),`activ`(5),`wheelchair`(5),`disabled`(5),`keywords`(5),`web`(5),`cemail`(5),`classes`(5),`meeting`(5))

I've got the php search working/indexed locally using the match and against

Chris

[coopster]

So it is indeed returning a result set now that you expected now? Good for you.

If I can get this working will I have to reapply the process each time a change happens manually or does it update automatically?

If you are asking if you will have to create the index again, no, you won't. The index is created once and used over and over again. Adding/Deleting/Updating rows in the table does not constitute a change to the index. ALTERing the table, may, but not the data within unless you have a new query that requires a different access path which will optimized the performance.

[chrisdgreen]

My problem is that I can get results running tests on a local dbase using a newer version of MySQL 4.0.17 (remote MySQL 3.23.56).
My Local test site indexes ok and returns results but the remote site tells me there is a problem with the sql even though it is the same as the one running locally, the only difference I think is that the fulltext index on the remote site is not complete/empty.

I have tried to refresh it recreate it etc but with no joy

If I'm running a search on the web content and that is content managed what is the best way to keep the index up to date with the content?

Cheers

Chris

[coopster]

What is the error message on the sql? That's where I would start.

As far as your second question, it is the same as I said before -- the content does not change the index you created. Your query will run and use the FULLTEXT index whether your user is searching for "cats" or "dogs". Or am I misunderstanding your question?

[chrisdgreen]

I'm sure the sql is OK as it works locally here it is

$sql = "SELECT id, oname, aims,
        MATCH(oname, otype1, otype2 ,olocation, aims, affil, activ, wheelchair, disabled, keywords, web, cemail, classes, meeting) 
        AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM profile 
        WHERE MATCH(oname, otype1, otype2 ,olocation, aims, affil, activ, wheelchair, disabled, keywords, web, cemail, classes, meeting) 
        AGAINST ('$searchstring' IN BOOLEAN MODE)AND live='1' ORDER BY score DESC"; 
 
  $result = mysql_query($sql) or die (mysql_error()); 

the error I get is
You have an error in your SQL syntax near 'BOOLEAN MODE) AS score FROM profile WHERE MATCH(oname, otype1, ' at line 3

again this is only remotely and I beleive this is because my remote dbase has not been indexed fully.

I have used SHOW INDEX FROM mydb.mytable;

LOCAL
profile 1 oname 1 oname A 1782 1 NULL FULLTEXT

REMOTE
profile 1 full_index 1 oname A NULL 5 NULL FULLTEXT

The cardinality for the remote is NULL where the working local is 1782 which is number of entries in the dbase.

?

As for the updating of the fulltext index, the client will be updating content within the database on a regular basis - using your example if they decide to change 'cats' to 'rabbits' within the content and the fulltext index isn't updated surely the quality of the search diminishes over time? Or am I missing something?

Thanks again

Chris

[coopster]

Have you tried rebuilding the remote FULL_TEXT index yet? Or REPAIR the table? There is this from the manual as well ...

With regard specifically to using the IN BOOLEAN MODE capability, if you upgrade from MySQL 3.23 to 4.0 or later, it's necessary to replace the index header as well. To do this, do a USE_FRM repair operation:

mysql> REPAIR TABLE tbl_name USE_FRM;

This is necessary because boolean full-text searches require a flag in the index header that was not present in MySQL 3.23, and that is not added if you do only a QUICK repair. If you attempt a boolean full-text search without rebuilding the indexes this way, the search returns incorrect results.

[chrisdgreen]

I have tried using the repair feature within phpmyadmin this appears to have no effect, I have altered the default 'REPAIR TABLE profile ' to 'REPAIR TABLE profile USE_FRM' this produced SQL error which I assume may be because of me using an older Mysql.

This is extremely frustrating, I have scoured the net and I am struggleing to find any info that might lead to a solution.

Cheers

Chris

[chrisdgreen]

Is it possible that my server hosts MySQL setup does not allow for this?
Is it possible to see like in PHP phpinfo() to see what the MySQL setup is?
If the setup may be the problem could anyone give me pointers as to what I'd need them to change or modify to get this working?

Cheers

Chris

[coopster]

The error tells all.

As of Version 4.0.1, MySQL can also perform boolean full-text searches using the IN BOOLEAN MODE modifier.

[dev.mysql.com...]

[chrisdgreen]

Thanks for that!

I'll speak with my hosts and see If they can upgrade it for me

Thanks for your help

Chris

[coopster]

You bet, Chris, good luck with the conversion ;)