Back-office search query optimization
Back-office search brought down from 2–4 minutes to a few seconds — even on 400,000 rows — thanks to full-text indexes and replacing ORs with UNIONs.
Ruby on Rails
MariaDB
SQL
Performance
Index full-text
UNION
Optimizing back-office search queries (offers, applications, the imported-listings occupation-tagging screen, the statistics dashboard). The search crossed jobs.entity_name and entities.name (in ~50% of cases these columns differ), with response times of 2 to 4 minutes.
By adding full-text indexes on those columns and replacing OR with UNION, results now come back in a few seconds, even for 400,000 rows. The optimization was extended to the occupation-tagging screen and the statistics reference dashboard.