2 Aug 25, 2010 18:46
Well, after some investigation, I think that (some of) the possible options to get a google-style search function are:
- using RLIKE, which is very much slower than using LIKE, about 20 times in our tests (our database: 50 000 records in items__item, the table weights 300Mb).
- You can still use a combination of LIKE and RLIKE (see in http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html the comment posted by Dave M on September 12 2004 3:44pm) which is much faster, but still slower than LIKE alone (from about 4.5 seconds to 3 seconds in our tests, using 3 words with AND in the standard search function)
- the best one: using FULLTEXT INDEX in post_title and post_content (wikipedia uses it). In our tests, searching for one word in phpmyadmin, using the standard b2evo where_keywords() query applied just to post_title, took 2.9006 sec without FULLTEXT, and 0.0879 sec whith the new index. The difference is huge, but there are some problems: FULLTEXT INDEX with standard minimum word length = 4 takes a lot of disk space. We tried first indexing just post_title, which is of course much smaller than post_content, and the total index space usage doubled inmediatly, so we didn't even tried with post_content. Second, if the table is already loaded with posts, it takes a lot of time to build the index/es.
So we left the search function as it is. But in the investigation found that using SQL_CALC_FOUND_ROWS the search function works faster. As Daniel Hahler says: "We might just use "SELECT SQL_CALC_FOUND_ROWS ..." and "FOUND_ROWS()"..! - available since MySQL 4 - would save one query just for counting!". Very much true, avoiding the most resource consuming query makes the system feel very well. We did it and the search times went down about 30/50%, from 3 sec to 1.5/2 sec (if the server works as it should, wich rarely happens...).