> [comparisons to Google...] While any speed up with a full table fulltext search would be helpful and useful, there are instances where the search is intersected with another column and the problem of search is therefore more complex but also leads to potential optimizations.
In our case we rarely do searches that use just the FTS index. More commonly, we do searches based on other columns and a FTS indexed text column. The WHERE clause restraints on the non-text column usually eliminate 99% of the rows. Fulltext indexes can not be combined with numeric columns in a single composite index, so as far as I can tell, MySQL performs the FTS then filters the results to meet the WHERE conditions. I believe the required amount of the index to be paged into main memory is much larger than might otherwise be needed (since the indexes are larger than available memory, and there a zillion other tables accessed simultaneously, this tends to effect all queries). So besides the obvious desire to lower the size of indexes, it would be beneficial if a combined index could lower the number of matching rows and the amount of the index needed to be in memory. I actually find it odd for people to not be using secondary columns in a fulltext search, other than to do something like Google (which does not lend itself to using SQL in the first place. Of course, I have a myopic view stemming from my own requirements and experience. All that said, I'm glad to have the FTS we have. Thanks Sergei for such a wonderful improvement to LIKE '%word%'! Sincerely, Steven Roussey http://Network54.com/?pp=e PS - is FTS search and update O(n^2)? --------------------------------------------------------------------- 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
