Mysql FULLTEXT search and dots

laanes

Hello,

My search query splits the keyword by dots and only returns results for the first section.

Eg. when searching for PROD134.485.125 I get results for PROD134

Is there a way around this?

I have tried adding double quotes around the keyword but doesn't seem to make a difference.

If it helps, the query:

SELECT DISTINCT(product_id), *,
(0.7 * ( MATCH (product_id) 
AGAINST('PROD645.46.759' IN BOOLEAN MODE)) + 
(0.1 * (MATCH (description) 
AGAINST ('PROD645.46.759' IN BOOLEAN MODE)))) AS Relavance 
FROM MY_TABLE 
WHERE ( MATCH (product_id,description) 
AGAINST('PROD645.46.759' IN BOOLEAN MODE)) >= 1

laanes

Anyone?

Is there anything I should be aware of while working with dots and FULLTEXT search?

Bonesnap

My guess is the IN BOOLEAN MODE modifier is affecting it in some way. The MySQL manual doesn't mention periods, but it does say certain characters have special meaning when this modifier is used.

laanes

Bonesnap;10996295 wrote:
My guess is the IN BOOLEAN MODE modifier is affecting it in some way. The MySQL manual doesn't mention periods, but it does say certain characters have special meaning when this modifier is used.

Thank You for pointing this out, I made some progress.

Manual says:

A phrase that is enclosed within double quote (“"”) characters matches only rows that contain the phrase literally, as it was typed.

So I've tried this:

SELECT DISTINCT(product_id), *,
(0.7 * ( MATCH (product_id) 
AGAINST('"PROD645.46.759"' IN BOOLEAN MODE)) + 
(0.1 * (MATCH (description) 
AGAINST ('"PROD645.46.759"' IN BOOLEAN MODE)))) AS Relavance 
FROM MY_TABLE 
WHERE ( MATCH (product_id,description) 
AGAINST('"PROD645.46.759"' IN BOOLEAN MODE)) >= 1

And it brings up only the product i searched for.

But here's another strange thing.

Inside every description I've got product_id without the preceding PROD, like so:

My description text, 164.354.821, some more description text.

Now when searching for 164.354.821, I get no results.

Does the comma affect anything perhaps?

Weedpacket

laanes wrote:
But here's another strange thing.

Inside every description I've got product_id without the preceding PROD, like so:

But the literal phrase you're searching for:

AGAINST ('"PROD645.46.759"' IN BOOLEAN MODE)))) AS Relavance

does contain "PROD". It's not going to find a product id that doesn't start with "PROD" for the simple reason that it literally does not start with "PROD".

laanes

Weedpacket;10996355 wrote:
But the literal phrase you're searching for:
AGAINST ('"PROD645.46.759"' IN BOOLEAN MODE)))) AS Relavance 
does contain "PROD". It's not going to find a product id that doesn't start with "PROD" for the simple reason that it literally does not start with "PROD".

To be more clear, I should have bolded both lines:

My description text, 164.354.821, some more description text.

Now when searching for 164.354.821, I get no results.

Weedpacket

laanes wrote:
Now when searching for 164.354.821, I get no results.

Is this with a different query from the one you showed in that post?

laanes

Weedpacket;10996357 wrote:
Is this with a different query from the one you showed in that post?

Yes, you would then match the fields against 164.354.821 like so:

SELECT DISTINCT(product_id), *,
(0.7 * ( MATCH (product_id) 
AGAINST('"164.354.821"' IN BOOLEAN MODE)) + 
(0.1 * (MATCH (description) 
AGAINST ('"164.354.821"' IN BOOLEAN MODE)))) AS Relavance 
FROM MY_TABLE 
WHERE ( MATCH (product_id,description) 
AGAINST('"164.354.821"' IN BOOLEAN MODE)) >= 1