Hi! On Oct 23, Monte Ohrt wrote: > I have a table with a full-text index that spans four text columns. It > seems that the more records a query matches, the longer it takes to > execute. Is that normal?
To quote my two-days-ago reply on exactly the same issue: =================================================================== Date: Mon, 21 Oct 2002 19:47:15 +0000 From: Sergei Golubchik <[EMAIL PROTECTED]> To: Jakob Nielsen <[EMAIL PROTECTED]> Cc: [EMAIL PROTECTED] Subject: Re: Obscure FULLTEXT search problems Hi! On Oct 21, Jakob Nielsen wrote: > > In scenario 1 I'm using this SQL-statement: > > SELECT * FROM tArticles WHERE ( Title LIKE '%HYDRO%' OR Body LIKE > '%HYDRO%' ) > > In scenario 2 I'm using this SQL-statement: > > SELECT * FROM tArticles WHERE ( MATCH( Title, Body ) AGAINST( > 'HYDRO')) > > My problem is that if I'm searching for a word (For instance HYDRO) > that are found in a lot of my articles, then scenario 1 is much faster > than scenario 2. But if the search word is in just a few of the > articles then scenario 2 is _much_ faster than scenario 1. Can someone > explain this to me? What do I do? Yes, it's a well known issue. There're two reasons for this. The first one is that when you select too many rows, it's faster to do a table scan - reading the file in sequential order - than scan the index and read row data in some random - implied by the index - order. That's why MySQL doesn't use index if it's expected to select more that one third of the table. But fulltext search (in 3.23 at least) cannot work without an index. The second thing is that fulltext search - as implemented in 3.23.x - uses some complicated statistics to produce adequate relevance rating (optimized for natural language queries). To do it, it keeps a list of all matched rows. Actually, not a list but a binary tree, so lookup time is O(log(N)). So search time is O(N*log(N)), while search time for LIKE grows linearly. Second issue can be resolved by using fulltext search IN BOOLEAN MODE (available since MySQL-4.0.2), it's not optimized for natural language queries and doesn't need these complicated relevance calculations. So it's O(log(N)) times faster than fulltext search in natural language mode and almost always faster than LIKE. 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
