php ajax live database search or one query that gets all results to search through

Question

I am building a "customer look up" tool for a company. The staff will be using this to pull records of their customers.

I was curious which was better and why..

Option 1:

Using php + ajax to search via onkeyup of search box. This would be a live search on key up that would query the database each time the key goes up. I would most likely approach this using WHERE CustomerName LIKE '%blah blah%' The database of customers could end up getting really big at some point...just wanted to add that in. How big? Well, thats up to the company.

OR

option 2:

Going ahead and calling all customers via one query that all get populated in a list on the website. As the staff member types in a search box, the pre-populated list gets filtered through. Again, the database of customers might get pretty large.

Either way, its alot of data to be sorting through. I need to keep this "onkeyup" style method without the use of a "search" button.

IF there is a better way, please also tell me about that as well.

I've already coded the part where it pre-populates the list and looked at the ajax option; however, I'm worried about performance once the customer list begins to grow. So, thats where this question comes in. This post is not to get specific code, just want to learn the best way to approach this project as well as future projects like this.

I will go ahead and post the code I'm currently using to filter through pre-populated data just for the record...

$(".searchbox").on('keyup', function () {
                if ($(this).val() != '') {
                    search(this);
                }
                else {
                    $(".data").show();
                    $.each($(".searchbox"), function () {
                        if ($(this).val() != '') {
                            $(this).keyup();
                        }
                    });
                }
            });

            function search(ele) {
                var val = $(ele).val() || '';
                if (val == '')
                    return;

                var dataclass = $(ele).attr('data-class');
                var SearchInText = '';
                $.each($(".data:visible"), function () {
                    SearchInText = $(this).find("td." + dataclass).text();
                    if (SearchInText.indexOf(val) == -1)
                        $(this).hide();
                });
            }

I can attest that once that list gets big, having it all in html elements and using js to show/hide matching ones, really bogs a web browser down... where each keypress hangs it for a good second or two (or more in the case of a list of 5000). So its better to go ahead and query the server. I would however suggest you put a wait-timer so that it won't fire off until the keyup has passed for a second or two. If a new keyup happens, it cancels out the prior. Reduces hits on someone quickly typing a word. — IncredibleHat, Jul 18 '18 at 03:03

score 2 · Accepted Answer · answered Jul 18 '18 at 03:13

If you expect the customer database to be really large, loading them all the browser in a one call is not a good a option. You will have to live load the customers based on the user input.

Live loading(onkeyup) could give much stress to your database. However, you can improve it with caching.

Since you are retrieving only the 'customer name' from the database, you can use a caching mechanism to keep customer names in the memory and send the filtered results to the browser. That way, you can avoid stress in your database.

You should delay the keyup handler to minimize request to server.

You can find how to do it in the following link.

How to delay the .keyup() handler until the user stops typing?

can you give a brief example of how to do the caching. Or a link to read more about it? The delay is a great idea. Thank you for that! — KDJ, Jul 18 '18 at 04:03
You can use `memcache`. check here https://dev.mysql.com/doc/refman/5.6/en/ha-memcached-interfaces-php.html and https://www.cloudways.com/blog/memcached-with-php/ — Johna, Jul 18 '18 at 04:56

php ajax live database search or one query that gets all results to search through

Option 1:

option 2:

IF there is a better way, please also tell me about that as well.

1 Answers1