100 search items is a lot to search on at once for any system. MySQL
has to search on a 100 terms, no way around that.
I think the only way to optimize your query is to narrow down the
search terms. Perhaps you can search on phrases instead of words?
After the first 5-10 terms have been matched the rest of the terms
being searched on are probably only eliminating a record or two, if
any at all. So the relevance of the later terms are pretty minimal.
If there are certain search terms that are used all the time, you may
want create categories and pre-assign articles to those categories.
Then you can search on categories and full text.
Regardless, the problem is the number of terms you are searching on.
It's like trying to find 100 names in a phone book. It would take you
a long time even though it's in alphabetical order.
On Aug 31, 2005, at 7:10 AM, Andrew Brosnan wrote:
Hello,
I need some help optimizing a query. The current query is as follows:
SELECT *,
MATCH(title) AGAINST ( 'S' IN BOOLEAN MODE ) AS score
FROM articles
WHERE MATCH(title) AGAINST ( 'S' IN BOOLEAN MODE );
'title' is a FULLTEXT index.
'S' is a query string that may have 100 search terms.
'articles' table has about 100,000 records.
The query runs OK (< 0.1 sec) as long as 'S' is small (< 5 terms), but
as the number of terms increase, it bogs down big-time.
EXPLAIN says:
*************************** 1. row ***************************
table: articles
type: fulltext
possible_keys: art_ft
key: art_ft
key_len: 0
ref:
rows: 1
Extra: Using where
1 row in set (0.00 sec)
Any suggestions?
Regards,
Andrew
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
