title: How to optimize "text search" for inverted index and relational database?
body: <h1>Update 2022-08-12</h1>
  <p>I re-thought about it and realized I was overcomplicating it. I found the best way to enhance this system is by using good old information retrieval techniques ie using 'location' of a word in a sentence and 'ranking' queries to display best hits. The approach is illustrated in this following picture.</p>
  <p><a href="https://i.stack.imgur.com/TQOJP.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TQOJP.jpg" alt="enhanced retrieval using location and ranking" /></a></p>
  <hr>
  <h1>Update 2015-10-15</h1>
  <p>Back in 2012, I was building a personal online application and actually wanted to re-invent the wheel because am curious by nature, for learning purposes and to enhance my algorithm and architecture skills. I could have used apache lucene and others, however as I mentioned I decided to build my own mini search engine.</p>
  <p><b>Question:</b> So is there really no way to enhance this architecture except by using available services like elasticsearch, lucene and others?</p>
  <hr>
  <h1>Original question</h1>
  I am developing a web application, in which users search for specific titles (say for example : book x, book y, etc..) , which data is in a relational database (MySQL).<br>
  <p>I am following the principle that each record that was fetched from the db, is cached in memory , so that the app has less calls to the database.<br></p>
  <p>I have developed my own mini search engine , with the following architecture:<br><img src="https://i.stack.imgur.com/4ZRYv.jpg" alt="Architecture diagram" />
  <br>
  This is how it works:<br></p>
  <ul>
  <li>a) User searches a record name</li>
  <li>b) The system check what character the query starts with, checks if query there : get record. If not there, adds it and get all matching records from database using two ways:</li>
  </ul>
  <ul>
  <li>Either query already there in the Table &quot;Queries&quot; (which is a sort of history table) thus get record based on IDs (Fast performance)<br></li>
  <li>Or, otherwise using <b>Mysql LIKE %% statement</b> to get records/ids (Also then keep the used query by the user in history table Queries along with the ids it maps to). <br>
  --&gt;Then It adds records and their ids to the <b>  cache</b> and Only the ids to the inverted index map.
  <ul>
  <li>c) results are returned to the UI</li>
  </ul>
  </li>
  </ul>
  <p>The system works fine, however I have <b> Two</b> main issues, that i couldn't find a good solution for (been trying for the past month):</p>
  <p><b>First issue:</b> <br>
  if you check point (b) , case where no query &quot;history&quot; is found and it has to use the <b>Like %%</b> statement : this process becomes <b>time</b> consuming when the query matches numerous records in the database (instead of one or two): <br></p>
  <ul>
  <li>It will take some time to get records from Mysql (this is why i used INDEXES on the specific columns)</li>
  <li>Then time to save query history</li>
  <li>Then time to add records/ids to cache and inverted index maps</li>
  </ul>
  <p><b>Second issue:</b> <br>
  The application allows users to add themselves new records, that can <b>immediately</b> be used by other users logged in the to application.<br>
  However to achieve this, inverted index map and table &quot;queries&quot; have to be updated so that in case any old query matches to the new word. For example if a <b>new</b> record &quot;woodX&quot; is being added, still the old query &quot;wood&quot; does map to it. So in order to re-hook query &quot;wood&quot; to this new record, here is what i am doing now:<br></p>
  <ul>
  <li>new record &quot;woodX&quot; gets added to &quot;records&quot; table</li>
  <li>then i run a <b>Like %%</b> statement to see which <b> already existing</b> query in table &quot;queries&quot; does map to this record(for example &quot;wood&quot;), then add this query with the new record id as a new row: [ wood, new id].</li>
  <li>Then in memory, update inverted index Map's &quot;wood&quot; key's value (ie the list), by adding the new record Id to this list</li>
  </ul>
  <p>--&gt; Thus now if a remote user searches &quot;wood&quot; it will get from <b> memory</b> : wood and woodX</p>
  <p>The <b>Issue </b> here is also <b>time consumption</b>. Matching all query histories (in table queries) with the newly added word takes a lot of time (the more matching queries, the more time). Then the in memory update also takes a lot of time.
  <br></p>
  <p>What i am <b>thinking</b> of doing to fix this time issue, is to return the desired results to the user <b> first</b> , then let the application POST an <b> ajax</b> call with the required data to achieve all these UPDATE tasks. But i am not sure if this is a bad practice or an unprofessional way of doing things? <br>
  So for the past month ( a bit more) i tried to think of the best optimization/modification/update for this architecture, but I am not an expert in the document retrieval field (actually its my first mini search engine ever built). <br></p>
  <p>I would appreciate any feedback or guidance on what i should do to be able to achieve this kind of architecture.<br>
  Thanks in advance.</p>
  <p><b>PS:</b> <br></p>
  <ul>
  <li>Its a j2ee application using servlets.</li>
  <li>I am using MySQL innodb (thus i cannot use full-text search option)</li>
  </ul>
  <hr>