Mysql – How to speed up full text query on a table with 4 million rows? (MariaDB)

configurationfull-text-searchinnodbmariadbMySQL

I have an InnoDB full-text table that serves the Ajax-powered search box 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, people's 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 maximize server potential through configuration.

FWIW SELECT VERSION() gives me 10.3.20-MariaDB-log.

Best Answer

How big is the dataset? SHOW TABLE STATUS.

It sounds like it is much less than innodb_buffer_pool_size, so it will probably reside in RAM once it is loaded.

There is no automatic way to get it loaded. You could write a script to access every row in the data and every index. But that is tricky and slow.

On the other hand, you can keep it loaded (at least across graceful shutdowns) by turning ON these: innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_shutdown.

Related Question