Which server-side search technology to use for a multi-table string search?

Question

I'm working on a platform that uses lots of database read power. Users perform lots of search queries, on various pages.

Very often, when a search query is being handled, we use multiple ways to perform the operation: we use MySQL natural search, we break the search phrase down and create variations of it, we assign a weight to each result based on the match, etc.

Now, we're planning to optimize our search efforts and we're mostly thinking about using Elasticsearch, running on an AWS server in our cloud.

Would you recommend anything better or more suitable than Elasticsearch?

Platform summary:

• Technology: PHP, MySQL
• Infrastructure: AWS
• Traffic: 200-700 concurrent users
• Number of tables while performing a search: 2-5
• A rough number of records to read while performing an advanced, generic search: 500M

Answer 1

A bit late but Elasticsearch was probably your best bet, since it's pretty much the industry standard currently.

The primary alternative is Sphinx Search, which afaik does what you listed and might be faster than Elasticsearch, at least it's certainly smaller. However it may be a bit of a struggle to set up the indexing, since the features are mostly oriented for a specific workflow and documentation is not too great due to the lesser popularity of the software, and because the choice of morphology plugins is limited and any new ones must be C libraries.