MySQL full-text issue

Some searches incorrectly return 0 results

[arran]

I have a fulltext index on 3 columns (A, B, C) on table X.

I updated 10 rows as follows:

update X set A = concat(A, ' <word Z>') where....

I then search for <word Z> and get 0 results instead of 10.

Can anyone explain this?

I tried rebuilding the fulltext index and also bouncing the MySQL instance to clear caches.

[physics]

I see you have a where clause in there.
Did you 'hand check' those rows to make sure the word actually got concatenated onto A?

[arran]

Hi physics - Yes I did.

[physics]

What was your search query?

[bibby]

Yep. Two reasons the values aren't changing could be:
:: they don't meet the where clause or
:: the query statement is flawed in another way (missing quotes, ect)

[bibby]

____ And are you searching with LIKE with %'s?

select * from X where A LIKE '%<word Z>%';

[arran]

What was your search query?

SELECT min(offer.price) as 'price',p1.ID as 'myid',p1.name, p1.desc, p1.image,category.cat1 as 'cat1' ,category.cat2 as 'cat2',cat2.desc as 'cat2name',brand.name as 'brand', brand.image as 'brand_image', MATCH(p1.name, p1.desc, p1.keywords) AGAINST ('$query') as 'relevancy' FROM product p1,brand,offer,productCategory,category, cat2 WHERE MATCH(p1.name, p1.desc, p1.keywords) AGAINST ('$query') and brand.ID = p1.brand and p1.ID = offer.product and p1.ID = productCategory.productID and productCategory.catID=category.ID and category.cat2 = cat2.ID and cat2.ID = (select min(catID) from productCategory where productID = p1.ID) group by p1.ID,p1.name, p1.desc, p1.image, brand, brand_image,cat1,cat2,cat2name order by relevancy desc

Where

$query

= <Word Z> and

p1.keywords

= <Column A>

And are you searching with LIKE with %'s?

I'm using fulltext search not the like operator.