CODEWITHSUNDEEP
javasqlsearcharchitecture
user1884155
user1884155

Reputation: 3736

Improve results of a "search" input field?

I have a database with 20,000 records. Each record has a name. When a user wants to view a record, he can visit a webapp and type the name of the record in an inputfield. While typing, results from the database would be shown/filtered matchin what the user typed. I would like to know the basic architecture/concepts on how to program this

I'm using the following language stack: frontend: html5/javascript (+ajax to make instant calls while user is typing) backend: java + jdbc to connect to simple sql database

My initial idea is:

  1. A user types text
  2. Whenever a character is entered or removed in the inputfield, make an ajax request to the backend
  3. The backend does a LIKE %input% query on the name field in the database
  4. All data found by the query is send as a json string to the frontend
  5. The frontend processes the json string and displays whatever results it finds

My two concerns are: the high amount of ajax requests to process, in conjunction with the possibly very heavy LIKE queries. What are ways to optimize this? Only search for every two characters they type/remove? Only query for the first ten results?

Do you know of websites that utilise these optimizations?

NOTE: assume the records are persons and names are like real people names, so some names are more common than others.

Upvotes: 1

Views: 204

Answers (2)

Sergey Alaev
Sergey Alaev

Reputation: 3972

Here are possible solutions:

  • Restirct search to prefix search - LIKE 'prefix%' can be executed efficiently using BTREE-type index.
  • Measure performance of naive LIKE '%str%' solution - it you are working on B2B application, database will likely load that table in memory and do queries fast enough.
  • Look at documentation for your database - there could be special features for that like inverted index
  • as @Stepan Novikov suggested, load your data in memory and search manually
  • Use specialized search indexers like SOLR or ElasticSearch (likely overkill for only 20k records)
  • If you are feeling ninja, implement your own N-gram index.

Upvotes: 1

Stepan Novikov
Stepan Novikov

Reputation: 1396

You can choose SPA approach - load all 20 000 names/ids to client side and then filter it in memory - it's supposed to be the fastest way with minimal load to the database and back-end

Upvotes: 2

Related Questions