Re: Fulltext searching very slow
I currently have:
have_query_cache: YES
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 16777216
query_cache_type ON
query_cache_wlock_invalidateOFF
though I’ve got plenty of RAM to tweak this. I don’t want to just throw RAM at poorly optimised queries though.
The simple query you mentioned takes 2 seconds for me (uncached). MATCH AGAINST with a full-text index takes 0.3 seconds. Keeping a full-text index only increased the database size by 30% in my case. That’s well worth while in the days where disk space is so cheap. The regular expression executes even quicker (0.2s), but as you say does not do partial words.
There’s no reason why the core can’t have an option for full-text in the settings page, with two different search methods depending on what is chosen. It would only take 20 or so lines of code.
