So I have now read lots of docs on FullText searching. From what I understand the update I have written is not the way to use the FullText search filter, so I have changed it back.

My sql query now reads (I have made it phpmyadmin friendly as I have been trying to tweak it to see if it will work):

SELECT DISTINCT
`CatalogueProduct`.`ClassName`,
`CatalogueProduct`.`Created`,
`CatalogueProduct`.`LastEdited`,
`CatalogueProduct`.`Title`,
`CatalogueProduct`.`StockID`,
`CatalogueProduct`.`BasePrice`,
`CatalogueProduct`.`URLSegment`,
`CatalogueProduct`.`Content`,
`CatalogueProduct`.`MetaDescription`,
`CatalogueProduct`.`ExtraMeta`,
`CatalogueProduct`.`Disabled`,
`CatalogueProduct`.`StockLevel`,
`CatalogueProduct`.`PackSize`,
`CatalogueProduct`.`Weight`,
`CatalogueProduct`.`TaxRateID`,
`CatalogueProduct`.`ID`,
CASE WHEN `CatalogueProduct`.`ClassName` IS NOT NULL
THEN `CatalogueProduct`.`ClassName` ELSE 'CatalogueProduct' END AS `RecordClassName`
FROM `CatalogueProduct`
WHERE (MATCH (``.`Title`,`Content`,`URLSegment`) AGAINST ('tgi*'))
AND (`CatalogueProduct`.`Disabled` = '0')
ORDER BY `CatalogueProduct`.`Title` ASC

The MATCH function is still missing the DB name, but erven if I change this. the search returns no results. I am finding this very confusing, somtimes I get results, other times I don't (when I should)...

How many matches should that be returning? and how many total rows in the table do you have?

Ok... Assuming you are using the native SilverStripe FulltextFilter I took a look at the code, and it doesn't quite look the same as the other filters (like PartialMatch or ExactMatch):

FullText filter:

	protected function applyOne(DataQuery $query) {
		return $query->where(sprintf(
			"MATCH (%s) AGAINST ('%s')",
			$this->getDbName(),
			Convert::raw2sql($this->getValue())
		));
	}

ExactMatch filter (and others):

	protected function applyOne(DataQuery $query) {
		$this->model = $query->applyRelation($this->relation);
		$modifiers = $this->getModifiers();
		$where = DB::getConn()->comparisonClause(
			$this->getDbName(),
			Convert::raw2sql($this->getValue()),
			true, // exact?
			false, // negate?
			$this->getCaseSensitive()
		);
		return $query->where($where);
	}

So maybe it's just missing the 'applyRelation' bit, which would lead to something like this:

	protected function applyOne(DataQuery $query) {
		$this->model = $query->applyRelation($this->relation);
		$modifiers = $this->getModifiers();
		$where = sprintf(
			"MATCH (%s) AGAINST ('%s')",
			$this->getDbName(),
			Convert::raw2sql($this->getValue())
		);
		return $query->where($where);
	}

And something similar for excludeOne(). But this is really just a shot in the dark :-)