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