5

I have a search function on my site that needs to search through ~2000 items (this table never changes, it will always have the same number of items) stored in MySQL. All it needs to do is search by name or 'LIKE' the name and return the id. I have thought of two approaches to this:

  1. Query database using ajax on keyup(), this seems like it would be expensive with many people searching
  2. Send all the data to client as a JSON file when they load the page and search through it using JavaScript to reduce the load on the database

Which approach is better? Or if you have a better approach I am open to suggestions.

Improve this question
1
  • 1
    The size of the data rather the number of items may be more helpful in deciding the right approach.
    – NoChance
    Commented Nov 11, 2017 at 10:41

2 Answers 2

Reset to default
4

There is a better approach.

First, do not send all the results by default in a json file. Your clients will have to download a huge file for potentially no reason at all, and you could have browser cache problem when the results change.

Second, key up searching is fine as long as you trigger it after X letters were written inside the search input. X depends on your business obviously. Also when opting for that solution, you should do server side pagination so that requests are lightweight and fast. They should not hurt your servers even with everyone spamming the search input.

This leads to the last point : implement server side caching for these queries. Many people search the same things, and caching could show a huge gain in performance and cost. The cache engine choice depends on the frequency of changes applied on these results.

Among all of the above, the second point is crucial, especially doing lightweight requests. As I see you're using MySQL, having smart indexing of your table since it never changes could lead to amazingly fast results.

Improve this answer
5
  • Hi Steve, thanks for your answer. MySQL already caches queries, correct? dev.mysql.com/doc/refman/5.7/en/query-cache.html I am using MySQL 5.7.19 so that's just before the query cache was deprecated.
    – obl
    Commented Nov 11, 2017 at 18:03
  • Also, my table consists of an id column (primary key), name, and some other fields (i will only be searching by LIKE name in this scenario). This should suffice for the indexing, right?
    – obl
    Commented Nov 11, 2017 at 18:05
  • Yes it caches query but that's still asking the database to do stuff which is heavy on servers/performance time compared to local cache. And no primary key indexing is not enough, your indexing strategy ultimately depends on the way searches will be done and what the data searched is.
    – Steve Chamaillard
    Commented Nov 12, 2017 at 12:39
  • 1
    Steve - I've seen your advise about server side pagination echoed many times and as a former RDBMS DBA, I cringe. Maybe I'm misuderstanding this, but if pagination is accomplished by breaking a single logical query into multiple queries by adding an additional WHERE clause or something that limits the range of results, this will slow down database access since it has to do many times the amount of work for a vastly greater number of queries.
    – DocSalvager
    Commented Nov 19, 2017 at 7:56
  • 1
    Pagination isn't accomplished by breaking a single logical query into multiple queries. The point is to not query the whole 2000 results but instead query 10 or 20 results, since you don't want to display so many results at once. 10 or 2000 results should be retrieved with the same amount of queries. I 100% agree with you that too many queries is a really bad idea (which is one of the reason of sending queries after X characters were input :)).
    – Steve Chamaillard
    Commented Nov 20, 2017 at 9:01
3

2000 items isn't really very much data if each item is a name and an id (unless the names are huge). Searching through it in Javascript will be plenty fast. If compressed when sent over the HTTP connection, it's probably under 10kb; even as Javascript objects, it should be under 200kb in memory. I've routinely see stylesheets that are twice that big.

If search is the primary function of this page, you should consider eager loading the data after the page itself is finished loading (in a DOM-ready callback or via async javascript or whatever). If users aren't necessarily going to search, you could possibly lazy load the data when the search field is focused. Since the data doesn't change, you could store it in a CDN for low-latency access around the globe without impacting your servers at all.

Improve this answer
1
  • 1
    Yeah, having only 2000 items, mysql doesn't seem the right fit for the search function backbone. Sending all data to the client after page load is by far the fastest solution and it'd be impossible to overload the server, since the select * query result will be cached.
    – winkbrace
    Commented Nov 17, 2017 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.