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?
> unsub=brent@stripped
>
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
