Hello Duncan, Thursday, February 27, 2003, 4:00:31 PM, you wrote:
SD> I having some strange things occur with doing fulltext searches. I'm not SD> exactly sure how to tackle this problem, so I figured I would send it out SD> and see if anyone has any ideas or has seen this before. SD> Background: SD> I have been investigating the use of MySQL to do fulltext searches on SD> emails. The client I work for operates several professional discussion SD> lists. Right now, list archive searching is done using WAIS, but we are SD> looking into the possibility of using MySQL instead. SD> Problem: SD> I created table 'discussions' to hold emails from multiple lists... SD> mysql> show create table discussions; SD> CREATE TABLE `discussions` ( SD> `ID` int(11) NOT NULL auto_increment, SD> `list` varchar(25) default NULL, SD> `sender` varchar(200) default NULL, SD> `subject` varchar(255) default NULL, SD> `body` text, SD> `send_date` datetime default NULL, SD> PRIMARY KEY (`ID`), SD> FULLTEXT KEY `subbody_idx` (`subject`,`body`), SD> FULLTEXT KEY `body_idx` (`body`), SD> KEY `list_idx` (`list`(10)) SD> ) TYPE=MyISAM SD> I did some tests of fulltext searches against the subject and body columns. SD> I used a search phrase that was SURE to return results. Everything seemed SD> to go fairly well except that it went a little slow when trying to do this SD> fulltext searches for two lists rather than just one list. I always got SD> results, but I wanted to try to increase the speed of the searches. You SD> have to understand that I did not particularly care about the results (just SD> that I got some). I was more concerned with speed because the main reason SD> the WAIS solution has hung around for so long is because it searches very SD> quickly. SD> So in the interest of a possible speed increase I decided to try putting SD> emails into a separate table for each discussion list. Each table for each SD> list was given the same structure... SD> mysql> show create table listA; SD> CREATE TABLE `listA` ( SD> `ID` int(11) NOT NULL auto_increment, SD> `sender` varchar(200) default NULL, SD> `subject` varchar(255) default NULL, SD> `body` text, SD> `send_date` datetime default NULL, SD> PRIMARY KEY (`ID`), SD> FULLTEXT KEY `subbody_idx` (`subject`,`body`), SD> FULLTEXT KEY `body_idx` (`body`), SD> KEY `sender` (`sender`(10)) SD> ) TYPE=MyISAM SD> Data was then placed in each of the tables with no errors or warnings SD> reported. SD> Now here's where the problem occurs. Fulltext searches do not produce any SD> results when using the exact same search phrase on most of the new tables. SD> These tables hold emails for lists that returned results from the larger SD> 'discussions' table. So, I can't quite understand what is occurring. I SD> have tried repairing the tables, dropping and recreating indexes, different SD> methods of inserting data into the tables. All to no avail. The fact that SD> one of the tables is returning results would make me think there is SD> something wrong with the content possibly. But the fact that the tables SD> that don't return results now contain the same content that was in the SD> aggregated "discussions" table and did return results for those same lists SD> (searching within lists was done using "list like 'listA'" for the SD> "discussions" table) would seem to suggest that there is nothing wrong with SD> the content. SD> I have encountered the same problem on three installations of MySQL. SD> - Ver 8.23 Distrib 3.23.49, for sun-solaris2.6 on sparc SD> - Ver 8.23 Distrib 3.23.54, for intel-linux on i686 SD> - Ver 8.39 Distrib 4.0.9-gamma, for intel-linux on i686 SD> Throughout my tests and attempts, I have received no errors or warnings. SD> So, I am really at a loss. SD> I've looked in the manual but haven't seen anything. Any ideas, hints, or SD> solutions would be greatly appreciated. SD> Thanks, SD> Duncan SD> ----------------------------------------------- SD> Duncan Salada SD> Titan Systems Corporation SD> 301-925-3222 x375 SD> --------------------------------------------------------------------- SD> Before posting, please check: SD> http://www.mysql.com/manual.php (the manual) SD> http://lists.mysql.com/ (the list archive) SD> To request this thread, e-mail <[EMAIL PROTECTED]> SD> To unsubscribe, e-mail <[EMAIL PROTECTED]> SD> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Maybe this could help? Try changing the name of the index on the second table you 've created -- Best regards, --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
