[SOLVED] Problem with fulltext search matching the word red

[spicerje]

Any help would greatly be appreciated.

 

I am using the following prepared statement

 

SELECT * FROM Items WHERE MATCH (name) AGAINST (?)

 

This seems to work most of the time.  Although, if I search on the word red I get no results.

 

SELECT * FROM Items WHERE MATCH (name) AGAINST ('red')

 

this returns nothing

 

SELECT * FROM Items WHERE MATCH (name) AGAINST ('white')

returns 29 rows

 

SELECT * FROM Items WHERE MATCH (name) AGAINST ('sunny')

returns 20 rows

 

If I use the following I get 29 rows returned which is what I would expect from the full text search

 

SELECT * FROM Items WHERE name like 'red%'

 

I have over 7000 rows.  I even tried a Boolean search in case it was returning over 50% of the rows.

 

SELECT * FROM Items WHERE MATCH (name) AGAINST ('+red' IN BOOLEAN MODE)

returns zero rows

 

SELECT * FROM Items WHERE MATCH (name) AGAINST ('+white' IN BOOLEAN MODE)

returns 29 rows

 

example data

 

Red Hot Bunny

 

White Speaker

 

 

Table structure

 

CREATE TABLE `Items` (

`itemnum` bigint(20) unsigned NOT NULL auto_increment,

`merchant_id` varchar(255) collate latin1_general_ci NOT NULL,

`product_id` varchar(255) collate latin1_general_ci NOT NULL,

`name` varchar(255) collate latin1_general_ci NOT NULL,

`merchant_name` varchar(255) collate latin1_general_ci NOT NULL,

`link` blob NOT NULL,

`thumbnail` varchar(255) collate latin1_general_ci NOT NULL,

`big_image` varchar(255) collate latin1_general_ci NOT NULL,

`price` varchar(255) collate latin1_general_ci NOT NULL,

`retail_price` varchar(255) collate latin1_general_ci NOT NULL,

`category` varchar(255) collate latin1_general_ci NOT NULL,

`sub_category` varchar(255) collate latin1_general_ci NOT NULL,

`description` varchar(255) collate latin1_general_ci NOT NULL,

`custom1` varchar(255) collate latin1_general_ci NOT NULL,

`custom2` varchar(255) collate latin1_general_ci NOT NULL,

`custom3` varchar(255) collate latin1_general_ci NOT NULL,

`custom4` varchar(255) collate latin1_general_ci NOT NULL,

`custom5` varchar(255) collate latin1_general_ci NOT NULL,

`last_updated` varchar(255) collate latin1_general_ci NOT NULL,

`status` varchar(255) collate latin1_general_ci NOT NULL,

`manufacturer` varchar(255) collate latin1_general_ci NOT NULL,

`partNumber` varchar(255) collate latin1_general_ci NOT NULL,

`merchantCategory` varchar(255) collate latin1_general_ci NOT NULL,

`merchantSubcategory` varchar(255) collate latin1_general_ci NOT NULL,

`shortDescription` varchar(255) collate latin1_general_ci NOT NULL,

`isbn` varchar(255) collate latin1_general_ci NOT NULL,

`upc` varchar(255) collate latin1_general_ci NOT NULL,

`all_items` blob NOT NULL,

`search` varchar(255) collate latin1_general_ci NOT NULL,

PRIMARY KEY  (`itemnum`),

FULLTEXT KEY `description` (`description`),

FULLTEXT KEY `name` (`name`),

FULLTEXT KEY `search` (`search`),

FULLTEXT KEY `All` (`name`,`description`,`search`)

) ENGINE=MyISAM AUTO_INCREMENT=7785 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

 

Mysql information

# Server version: 5.0.67-community-log

# Protocol version: 10

# Server: Localhost via UNIX socket

# User: XXXX

# MySQL charset: UTF-8 Unicode (utf8)

#

MySQL connection collation: utf8_unicode_ci

 

I have done a search in Google, this forum, and several others and have found nothing.  Looked up stop words and red is not a stop word.  So I just can not understand why it will return zero results if the word red is the search term?

[spicerje]

Never mind I found the problem.

 

full text search by default can not search for anything less than 4 characters.

 

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