Couple problems here...
Have a database with 36,316 emails archived.
emailarchive.emailbody has a FULLTEXT index.
If I query on a term that exists in the majority of the emails, it returns 0
rows and takes an incredible amount of time.
mysql> SELECT count('') FROM emailarchive where listnum = 1 and match
(emailbody) against ('shawn') group by'';
Empty set (7.19 sec)
The above query should return a total of 22,778.
mysql> SELECT count('') FROM emailarchive where listnum = 1 and match
(emailbody) against ('mysql') group by'';
+-----------+
| count('') |
+-----------+
| 121 |
+-----------+
1 row in set (0.03 sec)
Change nothing but the word you are searching on and it's relatively fast
(although it gets slower the more instances is there are)
mysql> SELECT count('') FROM emailarchive where listnum = 1 and match
(emailbody) against ('billing') group by'';
+-----------+
| count('') |
+-----------+
| 5062 |
+-----------+
1 row in set (1.43 sec)
The only thing I can think of is that the relevancy ranking causing both
problems (null set and speed problems) as it gets slower and slower the more
results there are until finally it just stops returning anything (maybe
there are so many the relevancy is zero?)
If that is the case, is there anyway to turn off the relevancy ranking
portion altogether or any suggestions to make it not so painfully slow? 7+
seconds for an "indexed" search on 36,000 records seems a little excessive
(the FileMaker database that we are upgrading from does the 'shawn' search
in 0.42 seconds and returns the correct results).
- Shawn
-------------------------------
Shawn D. Hogan
President, Digital Point Solutions
http://www.digitalpoint.com
(858) 452-3696
---------------------------------------------------------------------
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
