I have a Node.js REST API, working directly with a MySQL database via node-mysql.
I'm looking for a way to filter the database results on an endpoint in the most performatic way.
Currently, I have an endpoint /api/v2/matches, where my API code queries the database and outputs the results as a JSON response.
I'm planning to use filters on this endpoint. e.g. /api/v2/matches?filter[team]=TeamId
What is the best way/most performatic way to filter those database results in my code?
Should I:
Query the database for everything and use Array.prototype.filter() or similar on my results array to filter the data properly.
Assemble my query string programatically, depending on what is passed on req.query.filters object and query the database.
Please keep in mind that my current query for this endpoint (the one that returns all results) has a few INNER JOINS going on. I'm using LIMIT 50 on this query as well.
Disk access time and data transfer are the main bottlenecks here. However, the LIMIT 50 has an interesting implication on this. You should use the filters in the DB query since it will limit the amount of time the DB spends searching and it will also limit the amount of data transferred if your DB is remote (if your DB is not remote, the gains will be smaller but still there). The slight overhead incurred by building the query is negligible compared to this.
SELECT * queries are certainly designed for speed, but your DBMS will have to cover the entire dataset. You can get a better execution time with SELECT WHERE especially if you provide the appropriate index(es) in your DB schema since you're limited to 50 results, even with JOINs.
Also, keep in mind that SELECT WHERE queries have years upon years of research behind them and tricks to speed them up, whereas Array.prototype.filter is likely a very simple implementation of "if (lambda(x)) arr.append(x)" or something like that. There's a good reason pretty much every company out there uses it instead of filter.