Recently, I've been building a search page where the user can input their search criteria and the server side will use a scoring and sorting algorithm to return the 'best' results first. The current flow is like so:
- User inputs their search criteria.
- Server-side code (PHP) builds a query to fetch all the results that match that criteria.
- The server uses a function to assign a 'score' to each result.
- The server loops through all the results and orders the results from highest score to lowest score using a "quick sort" algorithm.
- The array is spliced in order to only take the top 15 results. These are then passed into the webpage to be displayed as results.
After consideration, this seems really inefficient. I'm querying the database for matching results each time the page is loaded or 'Load More' is clicked, scoring and then sorting each and every one, only to take 15 of these results.
Of course, I have to query all the matching results in order to provide the 'best' results for the whole table, rather than out of that 15.
I was thinking maybe a better way to do it, is sort and score the entire table via a cronjob and give each row a 'position rating'. When the user comes to load the webpage, it can just query the database for matching results and in the same SQL statement, order them by the 'position rating' column and then limit the results to 15 (and using offset for the AJAX query).
Would this process be much quicker? The only downside I could think of is that the ratings would be outdated, depending on how often the cronjob sorted the results.
Is it worth changing to this new process, or is my efficiency concern here not well founded?