Hi!
On Nov 20, Laurence Aronberg wrote:
> Hi. I'm doing a search of a 2-million-record book table. The table is
> searched very heavily, and while most queries are finished in a second or
> two, some take a disproportionately long time - any time a very common word
> such as 'john', which matches upwards of 80,000 records, is included in the
> query, the query will hog the server for several minutes.
>
> Queries matching less common words finish very quickly and are
> unproblematic. The problem is matching the common words.
>
> I don't know if anyone has any suggestions for fixing this, short of making
> 'john' and other common words a stopword, which isn't ideal. The only thing
> I could think of was given a query like this:
>
> match(author) against('daley') and match(author) against('john') and
> match(title) against('timbuktu')
>
> the server should ideally first find the set of 'daley' matches, and then,
> as it's an AND search, perform fulltext matching for 'john' only within that
> result set. Is there anyway to get this to happen - where a query includes
> several fulltext search clauses, it currently seems to evaluate each
> independently, which is very inefficient.
>
> Or is there some other way of resolving this problem?
Yes - for now you can use
MATCH xxx AGAINST ("some not-so-common words") AND xxx LIKE '%common_word%'
The problem with your approach was that in 3.23 and 4.0.0 fulltext
search engine cannot resolve the query by looking into row data only, it
has always to do index lookup - and thus it cannot do a MATCH for some
subset of the table rows. It's fixed already in the developmnet tree and
will be available with 4.0.1 version.
Regards,
Sergei
--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/
---------------------------------------------------------------------
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
