- 2,665
This is the first time I've dabbled in anything more complicated than the boring SELECT * FROM table WHERE column LIKE %SearchString% syntax. For a site I'm developing, I need to be able to actually perform a reasonably useful search on a large block of text.
There are three tables on the database I'm attempting to search using the SELECT * FROM table WHERE MATCH(column) AGAINST (SearchString) syntax. Each table contains a single column that I'm attempting to search. Each table has had CREATE FULLTEXT INDEX name ON table(column) run on it, and in investigating the problem, I have subsequently run REPAIR TABLE name QUICK to try and fix the problem. On one table, I have indexed a VARCHAR field, and this table works fine. On the other two tables, I have indexed a TEXT field, and these return no results.
One of the tables:
--
-- Table structure for table `blog`
--
CREATE TABLE IF NOT EXISTS `blog` (
`page` int(20) NOT NULL,
`version` varchar(20) NOT NULL,
`content` text NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`page`,`version`),
FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Stores blog posts.';
--
-- Dumping data for table `blog`
--
INSERT INTO `blog` (`page`, `version`, `content`, `date`) VALUES
(8, 'Current', 'There have been some exciting jQuery developments throughout April.', '2008-04-26 15:21:00'),
(9, 'Current', 'Progress in May will focus on expanding the AJAX functionality of the CMS, as well as the foundation of the administration system.', '2008-05-01 23:17:00');
I've included dummy data that I'm testing with because it's not sensitive and it might people better recreate the scenario I'm working with. If anyone is genuinely interested in helping, I'm more than happy to turn over phpMyAdmin access - this is being developed on a test server so I'm not stressed about giving out access to an isolated, test DB.
The query I'm attempting is:
SELECT * FROM blog WHERE MATCH (content) AGAINST ('jQuery')
I have also tried:
SELECT * FROM blog WHERE MATCH (content) AGAINST ('jQuery' WITH QUERY EXPANSION)
The table information lists 2 for the cardinality of the content index, but the query always returns an empty result set, regardless of what keywords I run it with. I don't know how to view the actual index data that MySQL has built to verify whether it's done so correctly. I'm also wondering if the CHARSET could be influencing the outcome.
Any advice or even direction to another forum or guide would be immensely appreciated. I have read both the MySQL manual and several online guides so far without success, but I guess I could still be missing something simple - that certainly feels the case to me.
Edit: Well after all of that, there was nothing wrong at all. Yes, that's right, the system actually works fine. For whatever reason, regardless of any REPAIR, CHECK or ANALYZE statements I ran against the table, it simple would not rebuild any form of useful index. The cardinality would change from None to the number of rows present, but searches using the above syntax would continue to return 0 rows. However, any *NEW* pages created *AFTER* adding the indexes work perfectly. So I guess the moral of the story is if you're going to support searching, make sure your indexes are present from the outset, if at all possible. I have no idea whatsoever how people can add indexes later and get it working, but I'm sure it's possible. Fortunately that's not a problem I have to address for now. So, this thread can be closed now.
There are three tables on the database I'm attempting to search using the SELECT * FROM table WHERE MATCH(column) AGAINST (SearchString) syntax. Each table contains a single column that I'm attempting to search. Each table has had CREATE FULLTEXT INDEX name ON table(column) run on it, and in investigating the problem, I have subsequently run REPAIR TABLE name QUICK to try and fix the problem. On one table, I have indexed a VARCHAR field, and this table works fine. On the other two tables, I have indexed a TEXT field, and these return no results.
One of the tables:
--
-- Table structure for table `blog`
--
CREATE TABLE IF NOT EXISTS `blog` (
`page` int(20) NOT NULL,
`version` varchar(20) NOT NULL,
`content` text NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`page`,`version`),
FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Stores blog posts.';
--
-- Dumping data for table `blog`
--
INSERT INTO `blog` (`page`, `version`, `content`, `date`) VALUES
(8, 'Current', 'There have been some exciting jQuery developments throughout April.', '2008-04-26 15:21:00'),
(9, 'Current', 'Progress in May will focus on expanding the AJAX functionality of the CMS, as well as the foundation of the administration system.', '2008-05-01 23:17:00');
I've included dummy data that I'm testing with because it's not sensitive and it might people better recreate the scenario I'm working with. If anyone is genuinely interested in helping, I'm more than happy to turn over phpMyAdmin access - this is being developed on a test server so I'm not stressed about giving out access to an isolated, test DB.
The query I'm attempting is:
SELECT * FROM blog WHERE MATCH (content) AGAINST ('jQuery')
I have also tried:
SELECT * FROM blog WHERE MATCH (content) AGAINST ('jQuery' WITH QUERY EXPANSION)
The table information lists 2 for the cardinality of the content index, but the query always returns an empty result set, regardless of what keywords I run it with. I don't know how to view the actual index data that MySQL has built to verify whether it's done so correctly. I'm also wondering if the CHARSET could be influencing the outcome.
Any advice or even direction to another forum or guide would be immensely appreciated. I have read both the MySQL manual and several online guides so far without success, but I guess I could still be missing something simple - that certainly feels the case to me.
Edit: Well after all of that, there was nothing wrong at all. Yes, that's right, the system actually works fine. For whatever reason, regardless of any REPAIR, CHECK or ANALYZE statements I ran against the table, it simple would not rebuild any form of useful index. The cardinality would change from None to the number of rows present, but searches using the above syntax would continue to return 0 rows. However, any *NEW* pages created *AFTER* adding the indexes work perfectly. So I guess the moral of the story is if you're going to support searching, make sure your indexes are present from the outset, if at all possible. I have no idea whatsoever how people can add indexes later and get it working, but I'm sure it's possible. Fortunately that's not a problem I have to address for now. So, this thread can be closed now.
