title: Bulding search engine for large database
body: <p>I'm building a fairly large database where I will have a lot of tables with various data.</p>
  
  <p>But each table has similar fields, for example video title or track title.</p>
  
  <p>Now the problem I'm facing is how to build a query which would look for a keyword match across five or more tables, keep in mind that each table can potentially have from 100k to 1million rows or in some cases even couple million rows.</p>
  
  <p>I think using joins or separate queries for each table would be very slow, so what I thought of is to make one separate table where I would store search data.</p>
  
  <p>For example I think it could have fields like these,</p>
  
  <p><code>id ---- username ---- title ---- body ---- date ---- belongs_to ---- post_id</code></p>
  
  <p>This way I think it would perform a lot faster searches, or am I totally wrong?</p>
  
  <p>The only problem with this approach that I can think of it is that it would be hard to manage this table because if original record from some of the tables is deleted I would also need to delete record from 'search' table as well.</p>