[SOLVED] What is wrong with this query...

[freakus_maximus]

I have been wracking my brain about this full text search. One works, one does not and cannot figure out why. I am including the sql dump so you can see my structures as well just in case I have something wrong there.

 

This one from the mysql website works fine returning 2 results:

 

SELECT * FROM `articles` 
WHERE MATCH (title,body) AGAINST ('database');

 

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl1
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: May 10, 2007 at 01:55 PM
-- Server version: 4.1.14
-- PHP Version: 5.0.5
-- 
-- Database: `tsapps`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `articles`
-- 

CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(200) default NULL,
  `body` text,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `title` (`title`,`body`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

-- 
-- Dumping data for table `articles`
-- 

INSERT INTO `articles` VALUES (1, 'MySQL Tutorial', 'DBMS stands for DataBase ...');
INSERT INTO `articles` VALUES (2, 'How To Use MySQL Well', 'After you went through a ...');
INSERT INTO `articles` VALUES (3, 'Optimizing MySQL', 'In this tutorial we will show ...');
INSERT INTO `articles` VALUES (4, '1001 MySQL Tricks', '1. Never run mysqld as root. 2. ...');
INSERT INTO `articles` VALUES (5, 'MySQL vs. YourSQL', 'In the following database comparison ...');
INSERT INTO `articles` VALUES (6, 'MySQL Security', 'When configured properly, MySQL ...');

 

This one does runs, but returns 0 results and the value I am searching for is in the first column:

 

SELECT * FROM `failpoints` 
WHERE MATCH (fp_description,fp_solution) AGAINST ('print');

 

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl1
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: May 10, 2007 at 01:56 PM
-- Server version: 4.1.14
-- PHP Version: 5.0.5
-- 
-- Database: `tsapps`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `failpoints`
-- 

CREATE TABLE `failpoints` (
  `fp_id` mediumint(9) NOT NULL auto_increment,
  `fp_description` longtext NOT NULL,
  `fp_solution` longtext NOT NULL,
  `application_id` mediumtext NOT NULL,
  `resource_id` mediumtext NOT NULL,
  `component_id` mediumtext NOT NULL,
  `service_id` mediumtext NOT NULL,
  PRIMARY KEY  (`fp_id`),
  FULLTEXT KEY `fp_description` (`fp_description`,`fp_solution`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `failpoints`
-- 

INSERT INTO `failpoints` VALUES (1, 'Webpage not displayed\r\n', 'Restart IIS and WWW Publishing service on web server\r\n', '1', '1', '1', '1,2');
INSERT INTO `failpoints` VALUES (2, 'Batch Print has not run or kicked off\r\n', 'Restart related services on IDS server\r\n', '1', '2', '2,3', '3,4');

 

 

 

[fenway]

Full-text indexing has some strange behiavior, including not returning any results when too many match -- you may want to read the refman page.

[freakus_maximus]

OK, the light just went on. This is where anything searched for if found in 50% of the results is treated as noise and is ignored.

 

Duh, I need to add some more sample info into the table and try again.

 

Thanks!