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:
backend: java + jdbc to connect to simple sql database
My initial idea is:
- A user types text
- Whenever a character is entered or removed in the inputfield, make an ajax request to the backend
- The backend does a LIKE %input% query on the name field in the database
- All data found by the query is send as a json string to the frontend
- 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.