[RESOLVED] Match Against in query for single byte

praveenp

Hi All,

I have table with fulltext indexed column.
Entered single digit number into that column and using Match Against in query but values are not fetching.
If Entered double digit number into that column then getting result.

Example:

CASE1: No Result Coming in below queries
1. SELECT FROM tblpractice WHERE MATCH(ids) AGAINST ('5' IN BOOLEAN MODE);
2. SELECT FROM tblpractice WHERE MATCH(ids) AGAINST ('5,6' IN BOOLEAN MODE);

CASE2: Results Coming in below query
SELECT * FROM tblpractice WHERE MATCH(ids) AGAINST ('15' IN BOOLEAN MODE);

Please any one can help me to get results incase of single digit numbers.

Appreciate your help

Thanks
Praveen.P

Pikachu2000

Have you checked to see what MySQL's minimum word-length parameter is set to for fulltext searches? I'm guessing it's probably set to 2 characters.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html

praveenp

Pikachu2000;10948396 wrote:
Have you checked to see what MySQL's minimum word-length parameter is set to for fulltext searches? I'm guessing it's probably set to 2 characters.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html

Hi Pikachu2000,
Yes your right, in my mysql server minimum word-length is set to 2. I appened "A" letter to ids and stored in to database, it is working fine now.

Thank you for your replay.

Thanks
Praveen

Weedpacket

I don't understand why you need full-text searching for IDs. Either the ID is there or it isn't - I don't see why someone might be interested in searching for IDs that are almost the same as the one given.

praveenp

Weedpacket;10948592 wrote:
I don't understand why you need full-text searching for IDs. Either the ID is there or it isn't - I don't see why someone might be interested in searching for IDs that are almost the same as the one given.

We are associating one note to many contacts, all these contact IDs are storing with comma separated into DB. Whenever we go to contact then need to show this associated note to this contact.

We can use FIND_IN_SET but this gives result late.

Thanks
Praveen

Weedpacket

Ouch: bad normalisation. Better to have an additional table relating note IDs with contract IDs (one row for each note ID/contract ID pair). Then you can search for a contract ID exactly and find out which note it belongs to; and you can go the other way, start with the note ID and find out which contracts it is associated with; you can even supply a whole list of contract IDs and get all the notes associated with them (or a whole list of note IDs and get all the contract IDs they're associated with). Basically, you'd be able use the database system the way it was designed and optimised to work.

praveenp

Thank you for replay,

This has been designed long back before me, currently i am working on bug fixes.
I will try to change db design.

Thanks
Praveen