What is the best search approach?

Question

MySQL

Task: to make fast search in several tables. For example, a user enters the word “bitcoin” into the search bar and the result should be a list of posts, tags, users.

1. The first option is to make separate queries to each table (entity)
   Pros:
   
   • Simple and transparent logic
     
   
   Minuses:
   
   • There are much more entities (tables by which to search), as a result, this is an expensive operation.
     
   
   
2. The second option is to make a common table in which I will put the entity string for searching, the type and id of all entities.
   title | type | id
   Ivan Ivanov | user | 1
   Apple news | post | 54
   Toster | tag | 12
   Pros:
   
   • Quick search in one table
     
   • Based on the results of a selection from one table, you can clearly find out which entities are there in order to make a separate query to a specific entity to select additional information. For example, we enter "Ivan" and the result from one table says that we only have users for such a request and do not need to go into posts and tags
     
   
   Minuses:
   
   • The table will grow very fast
     
   • You need to keep track of all entities and update the name if they have changed
     

Which approach is better in terms of performance and correctness of the database architecture? I also look forward to your decision.

Answer 1

It's better not to reinvent the wheel and use ElasticSearch or Sphinx