Mysql query problem....

Question

I have below values in my database.

1. been Lorem Ipsum and scrambled ever
2. scrambledtexttextofandtooktooktypetexthastheunknownspecimenstandardsincetypesett

Here is my query:

SELECT
  nBusinessAdID,
  MATCH (`sHeadline`) AGAINST ("text" IN BOOLEAN MODE) AS score 
FROM wiki_businessads
WHERE MATCH (`sHeadline`) AGAINST ("text" IN BOOLEAN MODE)
  AND bDeleted ="0" AND nAdStatus ="1"
ORDER BY score DESC, bPrimeListing DESC, dDateCreated DESC

It's not fetching first result, why? It should fetch first result because its contain text word in it. I have disabled the stopword filtering.

This one is also not working

SELECT
  nBusinessAdID,
  MATCH (`sHeadline`) AGAINST ('"text"' IN BOOLEAN MODE) AS score 
FROM wiki_businessads
WHERE MATCH (`sHeadline`) AGAINST ('"text"' IN BOOLEAN MODE)
  AND bDeleted ="0" AND nAdStatus ="1"
ORDER BY score DESC, bPrimeListing DESC, dDateCreated DESC

Thanks
Avinash

Answer 1

The full text search only matches words and word prefixes. Because your data in the database does not contain word boundaries (spaces) the words are not indexed, so they are not found.

Some possible choices you could make are:

• Fix your data so that it contains spaces between words.
• Use LIKE '%text%' instead of a full text search.
• Use an external full-text search engine.

I will expand on each of these in turn.

Fix your data so that it contains spaces between words.

Your data seems to have been corrupted somehow. It looks like words or sentences but with all the spaces removed. Do you know how that happened? Was it intentional? Perhaps there is a bug elsewhere in the system. Try to fix that. Find out where the data came from and see if it can be reimported correctly.

If the original source doesn't contain spaces, perhaps you could use some natural language toolkit to guess where the spaces should be and insert them. There most likely already exist libraries that can do this, although I don't happen to know any. A Google search might find something.

Use LIKE '%text%' instead of a full text search.

A workaround is to use LIKE '%text%' instead but note that this will be much slower as it will not be able to use the index. However it will give the correct result.

Use an external full-text search engine.

You could also look at Lucene or Sphinx. For example I know that Sphinx supports finding text using *text*. Here is an extract from the documentation which explains how to enable infix searching, which is what you need.

9.2.16. min_infix_len

Minimum infix prefix length to index. Optional, default is 0 (do not index infixes). Infix indexing allows to implement wildcard searching by 'start*', '*end', and 'middle' wildcards (refer to enable_star option for details on wildcard syntax). When mininum infix length is set to a positive number, indexer will index all the possible keyword infixes (ie. substrings) in addition to the keywords themselves. Too short infixes (below the minimum allowed length) will not be indexed.

For instance, indexing a keyword "test" with min_infix_len=2 will result in indexing "te", "es", "st", "tes", "est" infixes along with the word itself. Searches against such index for "es" will match documents that contain "test" word, even if they do not contain "es" on itself. However, indexing infixes will make the index grow significantly (because of many more indexed keywords), and will degrade both indexing and searching times.