We have a query of the following type which we are trying to optimize.
SELECT table_one.* FROM table_one INNER JOIN table_two ON table_two.fk_id = table_one.id WHERE (table_one.h_id = 20626 AND table_one.is_active = true) AND (Match(table_two.search_field) against ('+some_term*' IN boolean mode)) ORDER BY table_one.updated_date DESC LIMIT 20 OFFSET 0;
As evident, table two has a FULL TEXT SEARCH index on search_field. We are planning to get rid of table_two by moving the search_field column to table_one. That would remove join from the query thereby improving the execution time.
However, table_one is frequently updated. How does FTS index respond to frequent update?
Our gut feeling is that the index should be affected as long as the search_field column itself is not updated. But a bit of experienced hindsight would be great.
To add to it, is there any way to improve the ORDER BY clause? Any other suggestions/improvements would be appreciated.