> 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).
There's also a consideration about splitting the workload between the RDBMS and the application. I've improved the performance of applications by simplifying the SQL and making the Perl/C do more of the work. When it is successful (usually), it's because the application can be written to "understand" the implications behind the query results, and the language is more generalised than SQL. Paul Wilson iiNet Ltd --------------------------------------------------------------------- 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
