Since they mentioned that it is for search purpose, it is not clear from your post whether the table was kind of an inverted index or it was also storing records. If it was just an inverted index, I dont see a requirement to fetch latest 500 records by modified date or doing joins (but merge yes). But if they also storing records and answering queries related to those records then it makes sense to consider the other factors you have mentioned in your post.

It is not clear what the purpose of the DB was to begin with