I have an InnoDB full text table that serves the ajax-powered searchbox at the top of my website. I generate it with a daily script that pulls data from a dozen entity tables on the site, and amalgamates them all into one FT table for searching.
To give users the best experience (IMHO) I take whatever their input is, clean certain characters out of it (all full-text modifiers, for example), and then prepend every term with + and append them all with *.
So a search for "stack overflow" becomes +stack* +overflow*
The column that I'm searching on the FT table is small, with a typical character length of 30 characters. Event names, peoples names, geographical locations, that sort of thing. Not huge passages of text.
It works, but queries take on the order of 1 second to be returned.
EDIT: just after posting I've rebuilt the index and it's down to 0.4 seconds now - but I'd still like to improve it, if possible.
How could I change that to 0.1 seconds, or is that a pipe dream?
My server is a dual Xeon with 16 cores/32 threads and 128GB of memory. I serve a million pages or so each month, and rarely see server load above 1-2, with plenty of spare memory.
I wonder if I can somehow force this table to reside permanently in memory (rebuilding it after a server reboot or mysql restart only takes 30 seconds or so), and if that would help? Or maybe MySQL is already holding it in memory - how can I check?
I'm happy with the query itself, I don't think there's much that I can improve about it, but I know very little about how to maximise server potential through configuration.
SELECT VERSION() gives me