0

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.

  • 1
    table_one is frequently updated. How does FTS index respond to frequent update? If the field indexed with FT index do not alters during update query, the index is not altered. – Akina Jul 10 at 7:59
  • @Akina Can you suggest any improvement in the query ? – Subrata Das Jul 11 at 5:35
  • How many updates per second are you talking about? – Rick James Jul 11 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.