Hi!
On Nov 26, Mike Wexler wrote:
> I'd like to understand this better.
>
> Lets say I have a table with 4 fields
>
> itemKey INT(10) unsigned auto_increment primary key
> status enum("forsale", "sold")
> description TEXT
> price DECIMAL(10,2)
>
> And I do a
>
> SELECT * FROM table
> WHERE MATCH (description) AGAINST ("A really nice toy")
> AND status="forsale"
> ORDER BY MATCH (description) AGAINST ("A really nice toy")
> LIMIT 10
>
> And lets further state that there at 10,000,000 records in the table and
> 7,500,000 of them are for sale. Lets also say that the minimum word
> length is set to 3 (so toy is considered significant for the fulltext
> search) and there is a fulltext index on description.
>
> Lets also assume that there are 1,000,000 records with the word "really"
> , 1,000,000 records with the word "nice" and 500,000 records with the
> word "toy". Lets say there are only 500 records with all three words.
>
> Approximately how many records will this query need to look at to return
> a result? Approximately how long will it take?
First - fulltext engine does NOT looks at records at all for text search,
it builds a list of documents purely from the index. Of course, MySQL
can later retrieve actual rows, based on this list.
So, I'll answer the question "...how many index entries will this query..."
The query, as you wrote it, will look at all the 2,500,000 index entries.
> How would the following query compare:
>
> SELECT * FROM table
> WHERE MATCH (description) AGAINST ("+A +really +nice +toy")
> AND status="forsale"
> ORDER BY MATCH (description) AGAINST ("+A +really +nice +toy")
> LIMIT 10
In 4.0.1 this query will be absolutely identical to the first one.
As boolean fulltext search was not documented we took a liberty of
changing the syntax slightly. Now it IS documented.
The query
SELECT * FROM table
WHERE MATCH (description) AGAINST ("+A +really +nice +toy" IN BOOLEAN MODE)
ORDER BY MATCH (description) AGAINST ("+A +really +nice +toy" IN BOOLEAN MODE)
LIMIT 10
will abort the search when it retrieves the 500th document with all the
three words. It's impossible to say, how many index entries it will look
at.
The query
SELECT * FROM table
WHERE MATCH (description) AGAINST ("+A +really +nice +toy" IN BOOLEAN MODE)
LIMIT 10
will abort the search when it retrieves the 10th document with all the
three words. It's impossible to say, how many index entries it will look
at.
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
