FULL TEXT Search not querying all indexed columns

[AdRock]

I have added a full text search which only works for the first 2 columns of my index.  If i try and run a search that contains any data in the last 2 indexed columns, i get no results returned.

 

Here is my table strucure with the full text indexes

CREATE TABLE `carshare` (
  `id` int(4) NOT NULL auto_increment,
  `userid` int(4) NOT NULL default '0',
  `seats_available` int(1) NOT NULL default '0',
  `start_street` varchar(30) NOT NULL default '',
  `start_postcode` varchar(9) NOT NULL default '',
  `start_lat` varchar(9) NOT NULL default '',
  `start_long` varchar(9) NOT NULL default '',
  `end_street` varchar(30) NOT NULL default '',
  `end_postcode` varchar(9) NOT NULL default '',
  `end_lat` varchar(9) NOT NULL default '',
  `end_long` varchar(9) NOT NULL default '',
  `depart_time` time NOT NULL default '00:00:00',
  PRIMARY KEY  (`id`),
  KEY `seats_available` (`seats_available`),
  KEY `depart_time` (`depart_time`),
  FULLTEXT KEY `car_search` (`start_street`,`start_postcode`,`end_street`,`end_postcode`)
) TYPE=MyISAM AUTO_INCREMENT=26 ;

 

and here is the query i'm performin gon the database

SELECT id,userid,seats_available,start_street,start_postcode,start_lat,start_long,
    end_street,end_postcode,end_lat,end_long,TIME_FORMAT(depart_time,'%H:%i') as depart,
                MATCH(start_street, start_postcode, end_street, end_postcode) AGAINST('$address') AS score
                FROM carshare
            WHERE MATCH(start_street, start_postcode, end_street, end_postcode) AGAINST('$address')
            ORDER BY score

any ideas why the last 2 columns are not getting searched?

[fenway]

FULLTEXT indexes act funny... if they match too many rows, or not enough rows, they don't always return resulst.

[AdRock]

Would it be best just to use %LIKE% instead?

[fenway]

Depends on the nature & size of your datasets... you may want to check the refman for relevant info.  %LIKE% will be MUCH slower.

[AdRock]

There isn't going to be loads of records but i want to be able to search 4 columns for keywords.

 

Would it be possible to index the 4 columns like with full text or would there be no need or could i get with indexing each column seperately

[AdRock]

I actually found out why some of my records aren't getting results.  Some of them are FULL TEXT STOP WORDS which i never knew existed  and some of the words being searched are only 3 letters in length which brings me onto my final question

 

How do I search for a partial postcode such as BF1 * (where start is a wildcard)

[fenway]

See here.