Sample Header Ad - 728x90

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

0 votes
2 answers
1547 views
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.
Asked by Codemonkey (265 rep)
Dec 9, 2019, 01:19 PM
Last activity: Aug 3, 2025, 11:10 PM