Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to optimize "text search" for inverted index and relational database?
    primarykey
    data
    text
    <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> <p>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> <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: <ul> <li>Either query already there in the Table "Queries" (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> -->Then It adds records and their ids to the <b> cache</b> and Only the ids to the inverted index map.</li> </ul></li> <li>c) results are returned to the UI</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 "history" 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 "queries" have to be updated so that in case any old query matches to the new word. For example if a <b>new</b> record "woodX" is being added, still the old query "wood" does map to it. So in order to re-hook query "wood" to this new record, here is what i am doing now:<br></p> <ul> <li>new record "woodX" gets added to "records" table</li> <li>then i run a <b>Like %%</b> statement to see which <b> already existing</b> query in table "queries" does map to this record(for example "wood"), 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 "wood" key's value (ie the list), by adding the new record Id to this list</li> </ul> <p>--> Thus now if a remote user searches "wood" 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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload